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.

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

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:

• Firstly, we will define the column list from both tables, where we want to select data in the SELECT condition.
• Then, we will define the Left table, which is table 1 in the FROM clause.
• And lastly, we will describe the Right table, which is table 2 in the Left JOIN condition, and write the join condition after the ON keyword.

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:

CREATE TABLE Client(
client_id int primary key, 
client_name varchar not null, 
client_profession varchar not null, 
client_qualification varchar not null,
client_salary int );

The below command is used to create an Orders table:

Create table Orders
(client_id int primary key,
order_id int not null,
price int,
order_date Date Not null);

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:

INSERT INTO Client (client_id, client_name, 
client_profession, client_qualification, client_salary)
VALUES
(1, 'Emma Hernandez','Web Designer','BTech', 25000),
(2, 'Mia Clark','Software Engineer','BE',20000),
(3, 'Noah Rodriguez','Bussinessman','MBA',50000),
(4, 'Martha Brown','Doctor','MBBS',75000),
(5,'James Luther','HR','MBA',35000),
(6,'Maria Garcia','Astronaut','Msc', 100000),
(7,'Robert Smith','Software Tester','BTech',30000);

In the below command, we are inserting the values in the Orders table:

INSERT INTO Orders (client_id, order_id, price, order_date)
VALUES
(1, 101, 2000,'2020-05-14'),
(2, 102, 3500,'2019-08-30'),
(3, 103, 4000,'2020-06-23'),
(4, 104, 2500,'2017-12-11'),
(5, 105, 5000,'2018-10-26');

After creating and inserting the values in the Client and Orders table, we will get the following output on executing the below command:

Table1: Client

Select * from Client;

Output

Table2: Orders

Select * from Orders;

Output

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

SELECT Client.client_id, client_name, order_date, price 
FROM Client  
LEFT JOIN Orders 
ON Client.client_id = Orders.client_id;

Or we use the Left Outer Join keyword in place of Left Join keyword in the above query as both will give similar output:

SELECT Client.client_id, client_name, order_date, price 
FROM Client  
LEFT Outer JOIN Orders 
ON Client.client_id = Orders.client_id;

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

• In the above screenshot, the left join condition selects the records from the left table (Client), and it equates the values in the client_id, client_name column with the values in the order_date, price column from the Orders table.
• If these records are similar, then the Left join creates a new row, which has the columns that display in the Select Clause and adds the particular row to the result.
• Suppose, if the values are not similar, then the left join also generates a new row, which displays in the SELECT command, and it fills the columns that come from the right table (Orders) with NULL.

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:

SELECT c.Client_id, Client_name,order_date, price
FROM Client c 
LEFT JOIN Orders o 
ON c.client_id= o.client_id;

Output

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.

SELECT Client_id, Client_name, Client_profession, order_date, price
FROM Client  
LEFT JOIN Orders   
USING (client_id);  

Output

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:

SELECT Client_id, Client_name, client_qualification,order_date, price
FROM Client  
LEFT JOIN Orders   
USING (client_id) WHERE client_qualification ='MBA';

Output

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:

SELECT client_name, client_profession, order_id, order_date, price
FROM Client 
LEFT JOIN Orders   
USING(client_id) WHERE price=3500;

Output

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:

SELECT client_name, client_profession, order_id, order_date, price
FROM Client 
LEFT JOIN Orders ON price=3500;

Output

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:

CREATE TABLE Client_details (
Phone_id int primary key, 
Mobile_number bigint,
address varchar not null
);

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:

INSERT INTO Client_details (Phone_id,Mobile_number,address)
VALUES (1, 9976542310,'Florida'),
(2, 9869456700,'New York'),
(3, 7345672210,'Chicago'),
(4, 9088506466,'Houston'),
(5, 9476548901,'Los Angeles');

After creating and inserting the values in the Client_details table, we will get the following output on executing the below command:

Select * from Client_details;

Table3: Client_details

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:

SELECT Client.client_name, order_id, order_date,Mobile_number
FROM Client 
LEFT JOIN Client_details 
ON Client_id= phone_id 
LEFT JOIN Orders 
ON Client.client_id = Orders.client_id 
ORDER BY client_salary;

Output

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:

SELECT client_id, client_name, mobile_number 
FROM Client 
LEFT JOIN Client_details
ON client_id = phone_id  
WHERE Mobile_number IS NULL;  

Output

Once we implemented the above command, we will get the following result: