sajad torkamani

Example database

The rest of this post will assume an example blog database with two tables: posts and users

posts table

idtitlecontentuser_id
1Michael post 1Lorem ipsum1
2Michael post 2Lorem ipsum1
3Jim post 1Lorem ipsum2
4Jim post 2Lorem ipsum2
5Anonymous post 1Lorem ipsumNULL
6Anonymous post 2Lorem ipsumNULL

users table

idname
1michael@example.com
2jim@example.com
4pam@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.

SQL right join

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:

  1. Iterate through all the users table rows (the “right table”).
  2. Include the values of the left table columns if the join condition is met.
    1. Try to find a matching record from the posts table where the post.user_id column matches the users.id column of the users table row currently being iterated.
    2. If the join condition is met, include the column values from the matched row of the posts table.
    3. Otherwise, simply set their column values to NULL.

Example result

authorpost
michael@example.comMichael post 2
michael@example.comMichael post 1
jim@example.comJim post 2
jim@example.comJim post 1
pam@example.comNULL

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:

authorpost
michael@example.comMichael post 2
michael@example.comMichael post 1
jim@example.comJim post 2
jim@example.comJim post 1
pam@example.comNULL

Links

Tagged: SQL