How do SQL joins work?
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 |
Inner joins
An inner join returns all records from the left table that have a matching record in the right table.
Example query
Assuming the blog database mentioned at the start of this post, the following query will return all the posts records that have a corresponding record in users:
SELECT p.title post,
u.name author
FROM posts p
INNER JOIN users u ON p.user_id = u.id;
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 |
Note how the anonymous posts (i.,e posts with the IDs 5 and 6) that don’t have a user_id aren’t included in the result.
Inner joins are very useful when you want to select only records that have a corresponding record from another table. For example, you may want to query all students that have already been assigned to a supervisor.
Left joins
A left join returns all records from the left table along with any matching records from the right table. Unlike inner joins though, left joins will include all results 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.
Example query
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;
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.
Right joins
A right join returns all records from the right table along with any matching records from the left table. Where there are no matching records from the left table, their column values will be set to NULL.
Example query
The following query will return all users records, even if they don’t have a corresponding record in posts.
SELECT u.name author,
p.title post
FROM posts p
RIGHT JOIN users u ON p.user_id = u.id;
Example result
| author | post |
|---|---|
| michael@example.com | Michael post 2 |
| michael@example.com | Michael post 1 |
| jim@example.com | Jim post 2 |
| jim@example.com | Jim post 1 |
| pam@example.com | NULL |
Notice how we get the user pam@example.com even though she doesn’t have any associated posts.
Right joins seem rarer from my experience because they are typically written as left joins to improve readability. For example, the previous right join query can be re-written as:
SELECT u.name author,
p.title post
FROM users u
LEFT JOIN posts p ON p.user_id = u.id;
This will return the same result:
| author | post |
|---|---|
| michael@example.com | Michael post 2 |
| michael@example.com | Michael post 1 |
| jim@example.com | Jim post 2 |
| jim@example.com | Jim post 1 |
| pam@example.com | NULL |
Wrapping up
Here are some links that I found useful for explaining SQL joins.
You might also find it useful to practice your SQL skills using a platform like Codewars.