PostgreSQL – Joins
A PostgreSQL Join statement is used to combine data or rows from one(self-join) or more tables based on a common field between them. These common fields are generally the Primary key of the first table and Foreign key of other tables.
There are 4 basic types of joins supported by PostgreSQL, namely:
- Inner Join
- Left Join
- Right Join
- Full Outer Join
Some special PostgreSQL joins are below:
- Natural Join
- Cross Join
- Self Join
Let’s look into the 4 of the basic Joins in PostgreSQL.For the sake of this article, we will be setting up a sample database with the below commands in our psql shell:
- Create a database zoo.
CREATE DATABASE zoo;
- Create a table zoo_1.
CREATE TABLE zoo_1 ( id INT PRIMARY KEY, animal VARCHAR (100) NOT NULL );
- Create a table zoo_2.
CREATE TABLE zoo_2 ( id INT PRIMARY KEY, animal VARCHAR (100) NOT NULL );
- Insert data into zoo_1 table.
INSERT INTO zoo_1(id, animal) VALUES (1, 'Lion'), (2, 'Tiger'), (3, 'Wolf'), (4, 'Fox');
- Insert data into zoo_2 table.
INSERT INTO zoo_2(id, animal) VALUES (1, 'Tiger'), (2, 'Lion'), (3, 'Rhino'), (4, 'Panther');
Now, we have two tables zoo_1 and zoo_2 with two common animals and four different animals. Let’s also assume zoo_1 is the left table.
Inner Join
The below statement joins the left table with the right table using the values in the “animal” column:
SELECT zoo_1.id id_a, zoo_1.animal animal_a, zoo_2.id id_b, zoo_2.animal animal_b FROM zoo_1 INNER JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;
Output:
As seen in the above output, the inner join returns a result set that contains row in the left table that matches the row in the right table.
The Venn diagram for INNER JOIN is as below:
Left Join
The below statement joins the left table with the right table using left join (or left outer join):
SELECT zoo_1.id, zoo_1.animal, zoo_2.id, zoo_2.animal FROM zoo_1 LEFT JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;
Output:
As seen in the output above the left join returns a complete set of rows from the left table with the matching rows if available from the right table. If there is no match, the right side will have null values.
The Venn diagram for a LEFT JOIN is as below:
Right Join
The RIGHT JOIN or RIGHT OUTER JOIN works exactly opposite to the LEFT JOIN. It returns a complete set of rows from the right table with the matching rows if available from the left table. If there is no match, the left side will have null values.
The below statement joins the right table with the left table using the right join (or right outer join):
SELECT zoo_1.id, zoo_1.animal, zoo_2.id, zoo_2.animal FROM zoo_1 RIGHT JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;
Output:
The Venn diagram for a RIGHT OUTER JOIN is below:
Full Outer Join
The full outer join or full join returns a result set that contains all rows from both the left and right tables, with the matching rows from both sides where available. If there is no match, the missing side contains null values.
The below statement illustrates the full outer join:
SELECT zoo_1.id, zoo_1.animal, zoo_2.id, zoo_2.animal FROM zoo_1 FULL JOIN zoo_2 ON zoo_1.animal = zoo_2.animal;
Output:
The Venn diagram for a FULL OUTER JOIN is below:
Contact Us