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.
employee
comments
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.
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).