SQL left join
Example database
The rest of this post will assume an example blog database with two tables: posts and users
posts table
| id | title | content | user_id |
|---|---|---|---|
| 1 | Michael post 1 | Lorem ipsum | 1 |
| 2 | Michael post 2 | Lorem ipsum | 1 |
| 3 | Jim post 1 | Lorem ipsum | 2 |
| 4 | Jim post 2 | Lorem ipsum | 2 |
| 5 | Anonymous post 1 | Lorem ipsum | NULL |
| 6 | Anonymous post 2 | Lorem ipsum | NULL |
users table
| id | name |
|---|---|
| 1 | michael@example.com |
| 2 | jim@example.com |
| 4 | pam@example.com |
What is a left join?
A left join returns all records from the left table along with any matching records from the right table. Unlike an inner join, a left join will include all rows from the left table regardless of whether there is a matching record from the right table. Where there are no matching records from the right table, their column values will be set to NULL.
Let’s look at an example query to understand better.
Example query
Assuming the blog database mentioned at the start of this post, the following query will return all posts records, even if they don’t have a corresponding record in users.
SELECT p.title post,
u.name author
FROM posts p
LEFT JOIN users u ON p.user_id = u.id;When processing the above query, the MySQL engine will do something like the following:
- Iterate through all the
poststable rows (the “left table”). - Include the values of the right table columns if the join condition is met.
- Try to find a matching record from the
userstable where theusers.idcolumn matches thepoststable row currently being iterated. - If the join condition is met, include the column values from the matched row of the
userstable. - Otherwise, simply set their column values to
NULL.
- Try to find a matching record from the
Example result
| post | author |
|---|---|
| Michael post 1 | michael@example.com |
| Michael post 2 | michael@example.com |
| Jim post 1 | jim@example.com |
| Jim post 2 | jim@example.com |
| Anonymous post 1 | NULL |
| Anonymous post 2 | NULL |
Notice how unlike with the inner join query, the left join returns all six posts (including the anonymous posts) and displays
NULL for the columns from the users table for posts that don’t have a corresponding user record.When to use a left join?
Left joins are very useful when you want to combine data from the left table with data from other tables and don’t want to exclude any rows from the left table even if there aren’t matching records in the other tables.
For example, you may want to find all posts and their assigned authors if there any:
SELECT p.title post,
u.name author
FROM posts p
LEFT JOIN users u ON p.user_id = u.id;In this case, the query results will include all posts records even those that don’t have a corresponding record from the users table.