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.

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:

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:

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:

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:

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:

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

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:

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

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.

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):

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

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:

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):

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

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.

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:

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.