PostgreSQL
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.
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)
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)
SELECT tbl1.column_name, tbl2.column_name
FROM table_1 tbl1
LEFT JOIN table_2 tbl2
ON tbl1.column_X = tbl2.column_X;
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).
ADVERTISEMENT