SQL right 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 right join?
A right join returns all records from the right table along with any matching records from the left table. Unlike an inner join, a right join will include all rows from the right table regardless of whether there is a matching record from the left table. Where there are no matching records from the left 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 u.name author,
p.title post
FROM posts p
RIGHT 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
users
table rows (the “right table”). - Include the values of the left table columns if the join condition is met.
- Try to find a matching record from the
posts
table where thepost.user_id
column matches theusers.id
column of theusers
table row currently being iterated. - If the join condition is met, include the column values from the matched row of the
posts
table. - Otherwise, simply set their column values to
NULL
.
- Try to find a matching record from the
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.
Left joins vs right joins and when to use right joins
Right joins aren’t used very often. People tend to use 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 |