PostgreSQL - LEFT JOIN

In this tutorial we will be learning about LEFT JOIN in PostgreSQL.

This is the continuation of the JOIN tutorial. Feel free to check PostgreSQL - INNER JOIN tutorial.

We use the LEFT JOIN clause to join two tables and return all the rows from the left side table and return only matched rows from the right side table.

If there is no match present in the right side table then the LEFT JOIN will give us NULL.

Following image shows the left join between two tables table_a (left side table) and table_b (right side table).

In this tutorial we will be using the employee and comments table. Feel free to check the PostgreSQL - INNER JOIN tutorial to create the tables and insert sample data in the table for this tutorial.

Employee Table

SELECT * FROM employee;

 employeeId | firstName | lastName | score |   lastModifiedAt    |      createdAt      |  birthday  |        email
------------+-----------+----------+-------+---------------------+---------------------+------------+----------------------
 e01        | Yusuf     | Shakeel  |     0 | 2018-02-04 06:08:10 | 2021-01-01 01:01:01 | 1900-01-01 | yusuf@example.com
 e04        | Tin       | Tin      |     7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:03 | 1900-10-20 | tintin@example.com
 e05        | Bob       | Coder    |     7 | 2018-02-04 06:08:10 | 2018-01-01 01:02:10 | 1900-08-20 | bobcoder@example.com
 e02        | John      | Doe      |     9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-02-03 | johndoe@example.com
 e03        | Jane      | Doe      |     9 | 2018-02-04 06:08:10 | 2018-01-01 01:01:04 | 1900-05-20 | janedoe@example.com
(5 rows)

Comments Table

SELECT * FROM comments;


 commentId |     commentBody     |   lastModifiedAt    |      createdAt      | employeeId
-----------+---------------------+---------------------+---------------------+------------
         1 | Awesome             | 2018-01-01 02:03:04 | 2018-01-01 02:03:04 | e03
         2 | Hello World         | 2018-01-01 02:03:05 | 2018-01-01 02:03:05 | e03
         3 | Happy               | 2018-01-01 02:03:06 | 2018-01-01 02:03:06 | e01
         4 | This is my comment. | 2018-01-01 02:03:07 | 2018-01-01 02:03:07 | e04
         5 | Keep coding.        | 2018-01-01 02:03:08 | 2018-01-01 02:03:08 | e01
(5 rows)

LEFT JOIN syntax

SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
LEFT JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;

LEFT JOIN employee and comments table

In the following example we are going to list all the employee that comments. When using LEFT JOIN we will get NULL for those employee who have not yet commented.

So, to achieve this we will be LEFT joining the two tables on the column employeeId.

This will give us all the rows that have matching employeeId value in both the tables.

If there is no match found for employeeId in the comments table (right side table of the LEFT JOIN) then we will get NULL.

SELECT e."employeeId", e."firstName", e."lastName", c."commentId", c."commentBody"
FROM employee e
LEFT JOIN comments c
ON e."employeeId" = c."employeeId"
ORDER BY "employeeId";


 employeeId | firstName | lastName | commentId |     commentBody
------------+-----------+----------+-----------+---------------------
 e01        | Yusuf     | Shakeel  |         3 | Happy
 e01        | Yusuf     | Shakeel  |         5 | Keep coding.
 e02        | John      | Doe      |           |
 e03        | Jane      | Doe      |         2 | Hello World
 e03        | Jane      | Doe      |         1 | Awesome
 e04        | Tin       | Tin      |         4 | This is my comment.
 e05        | Bob       | Coder    |           |
(7 rows)

As you can see above employee e02 and e05 have not posted any comments so we are getting no value from the comments table (right side table in the LEFT JOIN).