Introducing the RIGHT JOIN in SQL

SQL Tutorials 8 min read
SQL RIGHT JOIN
Blog / SQL Tutorials / Introducing the RIGHT JOIN in SQL

The functionality of the RIGHT JOIN in SQL is identical to the LEFT JOIN, with the only difference being that the direction of the operation is inverted.

Side note: Make sure you have downloaded the database that we will be working with. If you haven’t, get it here.

First, let’s see what will be the result if we use a LEFT JOIN. The code that we will run looks like this:

SELECT

   m.dept_no, m.emp_no, d.dept_name

FROM

    dept_manager_dup m

         LEFT JOIN

    departments_dup d ON m.dept_no = d.dept_no

ORDER BY dept_no;

left join code

How to Implement the RIGHT JOIN in SQL?

Now, let’s implement a RIGHT JOIN, or a RIGHT OUTER JOIN (these two phrases are interchangeable!). We can do that by simply changing LEFT with RIGHT in the previous query we wrote.

24 rows returned

As you can see in the picture above, the rows we retrieved are different from the ones we got with the LEFT JOIN.

data science training

The Different Output

Let’s see why they are not the same.

First, this time, the “dept_manager_dup” table was set to be on the right side, while the “departments_dup” table was placed on the left.

dept_manager dup left departments_dup right

So, looking at the Venn diagram, imagine we are interested in the entire right circle, comprising the common area in the middle.

Venn diagram

What we carried out here is logically symmetrical to the operation we would perform with a LEFT JOIN. The Outer Records

Well, in our case, the outer records will be from the table located on the right side of the Venn diagram – the “dept_manager_dup” table. Think of it as if the “dept_manager_dup” table is joining the “departments_dup” table from the right side.

Right join

In our selection the department number is extracted from the “dept_manager_dup” table, we see 4 null values.

Right join 4 null values

If we substitute the table of this field with the “departments_dup” table, instead of having 4 null values at the top, we will have one null value, d001, and then – d010, and d011.

1 null value

How to Obtain the Same Output with a LEFT and a RIGHT JOIN?

Is it possible that whether we run a RIGHT JOIN or a LEFT JOIN with an inverted tables order, we will obtain the same output?

Yes, we will!

Important: Keep the same aliases as the ones used in the RIGHT JOIN.

Try writing the following code to see if the output will be identical:

SELECT

    m.dept_no, m.emp_no, d.dept_name

FROM

    dept_manager_dup m

        LEFT JOIN

    departments_dup d ON m.dept_no = d.dept_no

ORDER BY dept_no;

highlight output

Amazing as it may seem, but the output is the same.

What’s the Application of the RIGHT JOIN in SQL?

So, considering what we said in this tutorial, you could feel there is no sense using RIGHT JOINS instead of LEFT JOINS, given we can invert the table’s order. And you would be right because RIGHT JOINS are seldom applied in practice.

But they exist and are a good exercise for developing a sense of how outer joins work in MySQL.

right joins are seldom applied in practice

A Few Notes

First, if you hear the term “linking column”, remember it is the same as “matching column”. Ergo, we can summarize the functionality of a RIGHT JOIN in SQL in the following way:

When applying a RIGHT JOIN, all the records from the right table will be included in the result set. Values from the left table will be included only if their linking column contains a value coinciding, or matching, with a value from the linking column of the right table.

all records from the right table will be included in the result set

The Type of Relationship

In addition, when talking about relationships, LEFT and RIGHT JOINS are perfect examples of one-to-many relationships in MySQL! For instance, in our last example, when we used a LEFT JOIN, each department from the “departments_dup” table, as represented by the department number, could have been the department of one or more managers from the “dept_manager_dup”.

connected tables

A manager, who is also an employee, can belong to a single department only.

A manager, who is also an employee, can belong to a single department only.

This is an example of how the one-to-many relationship can be exhibited in a LEFT or RIGHT JOIN case.

Apply various selections and join combinations on these two tables to get used to this essential MySQL tool.

The Next Step

After doing that, you can move onto a more complicated concept. Feel free to embark on the journey where you will learn how to create more thought-provoking code by writing subqueries in SQL.

***

Eager to hone your SQL skills? Learn how to put theory into practice with our hands-on tutorials!

Next Tutorial: Write SQL Subqueries

Earn your Data Science Degree

Expert instructions, unmatched support and a verified certificate upon completion!

Leave a Reply

Your email address will not be published.

You have Successfully Subscribed!