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;

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.

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

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.

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

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.

In our selection the department number is extracted from the “dept_manager_dup” table, we see 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.

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;

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.

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.

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”.

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? Enroll in our SQL course.

Next Tutorial: Write SQL Subqueries