My Blog

PostgreSQL JOIN

—

by

Faris Khalil
in

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:

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

PostgreSQL JOIN

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.

PostgreSQL JOIN

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.

PostgreSQL JOIN

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.

PostgreSQL JOIN

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.

PostgreSQL JOIN

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:

PostgreSQL JOIN

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:

PostgreSQL JOIN

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:

    PostgreSQL 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.

    PostgreSQL JOIN
    • 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:

    PostgreSQL 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.

    PostgreSQL JOIN

    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.

    PostgreSQL JOIN

    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 JOIN

    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:

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

    PostgreSQL JOIN

    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.

    PostgreSQL JOIN

    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 JOIN

    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:

      PostgreSQL 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 JOIN

      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:

      PostgreSQL JOIN

      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:

      PostgreSQL JOIN

      Overview

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

      Comments

      Leave a Reply

      Your email address will not be published. Required fields are marked *