PostgreSQL Left Join
In this section, we are going to understand the working of ostgreSQL Left join, which is used to return data from the left table. We also learn how to use table-aliasing, WHERE clause, USING clause, and join multiple tables with the help of the PostgreSQL Left join clause.
What is PostgreSQL Left Outer Join or Left Join clause?
The PostgreSQL LEFT JOIN or Left Outer Join is used to return all rows from the left table and rows from the other table where the join condition is fulfilled defined in the ON condition. And if there are no corresponding records found from the right table, it will return null.
The Left Join can also be known as the Left Outer Join clause. Therefore, the Outer is the optional keyword, which is used in Left Join. In PostgreSQL, the Left join is parallel to the Inner Join condition.
The following Venn diagram displays the PostgreSQL Left join where we can easily understand that the Left Join returns all the data from the Left table and a similar data from the Right table:
PostgreSQL Left Join Syntax
The Left Join keyword is used with the SELECT command and must be written after the FROM Keyword.
In the above syntax, table1 is referring to the left table, and table2 is referring to the right table, which implies that the particular condition will return all records from table1 and matching records from table2 according to the defined join condition.
We will follow the below steps to join the Left and Right tables with the help of the Left or Left Outer Join condition:
Example of PostgreSQL Left join
Let us see an example to understand how the PostgreSQL Left join works:
To join two tables by using PostgreSQL Left Join
For this, we will create two tables named Client and Orders table with the help of the CREATE command and insert some values using the INSERT commandM.
Firstly, we are going to create Client and Orders tables by using the CREATE command:
The below command is used to create an Orders table:
The Client and Orders tables have been successfully created after executing the above commands.
Once the both the table have been generated, we are ready to insert some values into it by using the INSERT command as follows:
In the below command, we are inserting the values in the Orders table:
After creating and inserting the values in the Client and Orders table, we will get the following output on executing the below command:
After executing the above command, we will get the data from the Orders table:
The below query is used to select records from both tables (Client and Orders):
Or we use the Left Outer Join keyword in place of Left Join keyword in the above query as both will give similar output:
Once we implemented the above command, we will get the below result:
Note: When a row from the Client table does not have a matching row in the Orders table, the value of the order_date and price column of the unmatched row would be NULL.
Working of PostgreSQL Left join
Table-aliasing with PostgreSQL Left Join
We will use table aliases to assign the joined tables short names to make the command more understandable.
Mostly, the tables we want to join will have columns with a similar name like the Client_id column. In the below command, we will use the table aliasing, and it returns a similar outcome as above:
Once we implemented the above command, we will get the below output:
PostgreSQL Left join with USING Clause
In this, we will see how the PostgreSQL Left join works with the USING clause.
Both the tables (Client and Orders) contain similar column name Client_id; that's why we can use the USING clause to get the values from the tables.
In the below example, we are using the USING clause in the Left join, which returns the values Client_id, Client_name, Client_prof, price, and order_date as both tables have a similar Client_id column.
After executing the above statement, we will get the below result:
PostgreSQL Left join using WHERE clause
Here, the WHERE clause allows us to return the filter outcome. And we can also use the WHERE condition with the Left join.
In the below example, we will select rows from both tables Client and Orders where client_qualification is equal to MBA:
After successful execution of the above command, it will give the below output:
Difference between WHERE and ON clause in PostgreSQL LEFT JOIN
We can see the below command to understand the differences between WHERE, and ON clauses in the PostgreSQL Left join.
In the Left Join, the WHERE and ON clause gives us a different output.
Firstly, we are using the WHERE Clause with the Left join as we can see in the below command:
Once we implemented the above query, we will get the following output:
Now, we are using the ON Clause with the Left join as we can see in the below command:
After executing the above command, we will get the below result:
Note: In PostgreSQL, the Inner join will always return a similar output if we using the WHERE and ON clauses into the command.
To join various tables using PostgreSQL Left JOIN
In the above section, we have already created two tables as Client and Orders. Now, we are using the Left join to combine the various tables and get the records from that particular table.
So, we will create one more table as Clieint_ details by using CREATE command as we can see in the following command:
Once the Clieint_ details table has been created successfully, we will insert some values into it with the help of INSERT command as we can see in the following command:
After creating and inserting the values in the Client_details table, we will get the following output on executing the below command:
Now, we will join multiple tables such as Client, Orders, and Client_details with the help of PostgreSQL Left Join as we can see in the following statement:
After successful execution of the above command, we will give the below result:
To get unmatched records by using of PostgreSQL LEFT JOIN clause
If we want to get the data from the table, which does not contain any similar row of data from another table, so in those cases, we will use the PostgreSQL LEFT JOIN clause.
As we can see in the below example, the LEFT JOIN clause is used to identify a client who does not have a Mobile_number:
Once we implemented the above command, we will get the following result: