PostgreSQL JOIN

In this section, we are going to understand the working of several types of PostgreSQL joins, such as Inner join, Left join, Right join, and Full Outer join in brief.

PostgreSQL JOINS are used with SELECT command, which helps us to retrieve data from various tables. And we can merge the Select and Joins statements together into a single command. Whenever we want to get records from two or more tables, we will execute the joins commands.

It is used to merge columns from one or more tables according to the data of the standard columns between connected tables. Usually, the standard columns of the first table are primary key columns and the second table columns are foreign key columns.

In PostgreSQL, we have various types of joins which are as follows:

• Inner join
• Left join
• Right join
• Cross join
• Full outer join
• Natural join
• Self-join.

The below image displays most importantly used PostgreSQL joins, which we are going to explain in this section of the PostgreSQL tutorial.

Example of PostgreSQL Joins

Let us see some examples of different types of PostgreSQL joins:

Here, we will be creating and inserting the two different tables where we perform actions on several types of joins:

In the below example, we will use the Create command to create a Luxury_cars table.

CREATE TABLE Luxury_cars (
L_ID INT PRIMARY KEY,
luxury_car_names VARCHAR (250) NOT NULL);

Output

Once we execute the above command, we will get the below message, which displays that the Luxury_cars table has been created successfully.

Here again, we will use the Create command to create a Sports_cars table as follows:

CREATE TABLE Sports_cars (
S_ID INT PRIMARY KEY,
sports_car_names VARCHAR (250) NOT NULL);

Output

Once we executed the above command, we will get the below message, which displays that the Sports_cars table has been created successfully.

After that, we will insert some values in the Luxury_cars table by using the INSERT command:

INSERT INTO Luxury_cars (L_ID, luxury_car_names)
VALUES
(1, 'Chevrolet Corvette'),
(2, 'Mercedes Benz SL Class'),
(3, 'Audi A7'),
(4, 'Genesis G90'),
(5,'Lincoln Continental');

Output

After executing the above command, we will get the below message that the values have been inserted successfully into the Luxury_cars table.

Just like we inserted the value in the Luxury_cars table, we will insert the values into the Sports_cars table as well with the help of Insert command:

INSERT INTO Sports_cars (S_ID, sports_car_names)
VALUES
 (1, 'BMW Z4'),
 (2, 'Nissan 370Z'),
 (3, 'Chevrolet Corvette'),
 (4, 'Mercedes Benz SL Class'),
(5,'Subaru BRZ');

Output

After executing the above command, we will get the below message that the values have been inserted successfully into the Sports_cars table.

The above tables have some similar cars, for example, Chevrolet Corvette and Mercedes Benz SL Class.

Now we will use the SELECT command to get the following data from the Luxury_cars table:

Select * from Luxury_cars;

Output

After executing the above command, we will get the below output:

We will get the following data from the Sports_cars table by using the SELECT command:

Select * from Sports_cars;

Output

After executing the above command, we will get the below result:

Now, let us see the working of different types of PostgreSQL Joins in real-time:

PostgreSQL Inner join

The PostgreSQL INNER JOIN is used to return all rows from various tables where the join condition is fulfilled.

Syntax of PostgreSQL Inner Join

SELECT columns  
FROM table1   
INNER JOIN table2  
ON table1.column = table2.column;  

The below visual representation shows the working of PostgreSQL inner join:

For Example

We will take the above tables (Luxury_cars and Sports_cars) to understand the PostgreSQL inner join.

The below command will join the first table (Luxury_cars) with the second table (Sports_carsv) by matching the values in the luxury_car_name and sports_car_name columns:

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
INNER JOIN Sports_cars
ON luxury_car_names= sports_car_names;

Output

Once we implemented the above command, we will get the below result where we can see the matched rows data from Luxury_cars and Sports_cars tables.

• The inner join is used to analyze each row in Table A (Luxury_cars).
• It equates the records in the luxury_car_name column with records in the sports_car_name column of each row in Table B (Sports_cars).
• If these records are similar, then the inner join creates a new row containing columns from both tables and enhances the particular row into the output.

PostgreSQL Left join

The PostgreSQL LEFT JOIN is used to return all rows from the left table, which can define in the ON condition and only those rows from the other table where the join condition is satisfied.

Syntax of PostgreSQL Left join

SELECT columns  
FROM table1  
LEFT JOIN table2  
ON table1.column = table2.column;

The below visual representation displays the working of PostgreSQL Left join:

For Example

In the below command, we are going to use the Left Join condition to join the Luxury_cars table with the Sports_cars table.

In the Left join clause, Table A or the first table is known as the Left table, and Table B or the second table is known as the Right table.

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
LEFT JOIN Sports_cars
ON luxury_car_names= sports_car_names;

Output

Once we implemented the above command, we will get the below result.

Working of PostgreSQL Left Join

• In the above screenshot, the left join condition selects the records from the left table ( Luxury_cars), and it equates the values in the luxury_car_names column with the values in the sports_car_names column from the Sports_cars table.
• If these records are similar, then the left join creates a new row, which is having the columns of both tables and adds the particular row to the result as we can see the Row1 and Row2 in the above output.
• Suppose, if the values are not similar, then the left join also generates a new row, which involves the columns from both tables and adds it to the outcome.
• However, it fills the columns of the right table (Sports_cars) with null as we can see the Row3, Row4, and Row5 in the output.

PostgreSQL Left Join with Where clause

We can also use the left join with a WHERE condition. In the below example, we will select rows from the left table (Luxury_cars), which does not contain similar rows in the right table (Sports_cars):

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
LEFT JOIN Sports_cars
ON luxury_car_names= sports_car_names
WHERE S_ID IS NULL;

Output

Once we implemented the above command, we will get the below output, which displays those records whose S_ID is NULL.

Note: We can use both the LEFT JOIN and LEFT OUTER JOIN equivalently because the working of Left join and Left outer join are similar.

The below visual representation explains the Left join that returns rows from the Left table, which does not contain the similar rows in the right table:

PostgreSQL RIGHT JOIN

The PostgreSQL RIGHT JOIN is used to return all rows from the Right table, which can define in the ON condition and only those rows from another table where the join condition is fulfilled.

The RIGHT JOIN will get the data from the right table as it is the opposite of the LEFT JOIN.

Syntax of PostgreSQL Right Join

SELECT columns  
FROM table1  
RIGHT JOIN table2  
ON table1.column = table2.column;  

The below Venn diagram displays the working of PostgreSQL Right join:

For Example

The below command is used to represent the working of Right join where we join the Luxury_cars table with the Sports_cars table:

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
RIGHT JOIN Sports_cars
ON luxury_car_names= sports_car_names;

Output

After executing the above command, we will get the below output:

Working of PostgreSQL Right join

• In the above image, the RIGHT JOIN equates each value in the Sports_car_names column of every row in the Sports_cars table with each value in the luxury_cars_name column of all row in the Luxury_cars table.
• If these values are similar, then the right join generates a new row, which carries the columns from both tables (Luxury_cars and Sports_cars).
• Suppose, if the values are not similar, then the right join also produced a new row, which involves the columns from both tables and enhances it to the output.

PostgreSQL Right Join with Where clause

In the same way, we can also use the right join with a WHERE condition. For example, we will use the where clause to select rows from the right table (Sports_cars), which does not contain similar rows in the left table (Luxury_cars):

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
RIGHT JOIN Sports_cars
ON luxury_car_names= sports_car_names
WHERE L_ID IS NULL;

Output

After executing the above command, we will get the below output, which displays those records whose L_ID is NULL.

Note: We can use both the RIGHT JOIN and RIGHT OUTER JOIN equivalently because the working of Right Join and Right outer join are similar to each other.

The below visual representation explains the Right join that returns rows from the Right table, which does not contain the similar rows in the left table:

PostgreSQL Full Outer Join

The FULL OUTER JOIN is used to return all records when there is a match in the left table or right table records.

Syntax of PostgreSQL Full Outer Join

SELECT columns  
FROM table1  
FULL OUTER JOIN table2  
ON table1.column = table2.column;  

The below Venn diagram displays the working of PostgreSQL Full Outer join:

For Example

The below command is used to represent the working of the Full Outer join to join the Luxury_cars table with the Sports_cars table.

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
FULL OUTER JOIN Sports_cars
ON luxury_car_names= sports_car_names;

Output

After executing the above command, we will get the below result:

PostgreSQL Full Outer Join using where clause

The below Venn diagram displays the full outer join that returns rows from a table, which does not contain the matching rows in the other table:

To return rows in a table that do not have matching rows in the other, we will use the full outer join with a WHERE clause like this:

SELECT L_ID, luxury_car_names, S_ID, sports_car_names
FROM Luxury_cars
FULL OUTER JOIN Sports_cars
ON luxury_car_names= sports_car_names
WHERE L_ID IS NULL OR S_ID IS NULL;

Output

Once we execute the above command, we will get the below result:

Overview

In this section, we have learned the working of several kinds of PostgreSQL joins, which combine data from various connected tables.