`RIGHT JOIN` and How to Use It

Let’s start with the definition. RIGHT JOIN is a type of join that returns all rows from the right table (that’s why it’s called “right”), and only matching rows from the left table. If there’s no matching data in the left table for a row in the right table, you’ll get NULL values for the left table’s columns.

You already know about LEFT JOIN—this is almost the same thing, just “mirrored.” The main difference is which table is considered the main one: left or right.

Syntax of RIGHT JOIN

The syntax for RIGHT JOIN is pretty much identical to LEFT JOIN. Here’s how it looks:

SELECT
    columns
FROM 
    left_table RIGHT JOIN right_table
ON 
    join_condition;

Pay attention to the order of the tables:

1. left table goes before RIGHT JOIN;
2. right table comes after the RIGHT JOIN keyword.

Let’s say we have two tables:

students — a list of students:

enrollments — records of students enrolling in courses:

Now we want to get a list of all enrollment records, plus info about the students if they exist. If a student isn’t found, we’ll see NULL instead of student info.

The query using RIGHT JOIN will look like this:

SELECT
    enrollments.enrollment_id,
    enrollments.course_name,
    students.name AS student_name
FROM 
    students RIGHT JOIN enrollments
    ON 
    students.student_id = enrollments.student_id;

Result:

Here you can see that all records from the enrollments table (the right table) are in the result, even if there’s no match in the students table (the left table).

Comparing RIGHT JOIN and LEFT JOIN

If you already get how LEFT JOIN works, you might be thinking: “So why do we even need RIGHT JOIN? Why not just use LEFT JOIN?” Good question! Let’s break down the differences and when you might actually want to use RIGHT JOIN.

1. Data direction

   • LEFT JOIN: returns all rows from the left table, no matter if there’s a match in the right table.
   • RIGHT JOIN: returns all rows from the right table, even if there’s no match in the left table.

2. Choosing the main table Usually you pick LEFT or RIGHT depending on which table (left or right) is more important to you and what order feels more natural for your query.

3. Equivalence Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the order of the tables. For example:

   -- RIGHT JOIN
   SELECT *
   FROM students RIGHT JOIN enrollments
   ON students.student_id = enrollments.student_id;
   
   -- Equivalent LEFT JOIN
   SELECT *
   FROM enrollments LEFT JOIN students
   ON students.student_id = enrollments.student_id;

So if you’re not a fan of RIGHT JOIN, you can always avoid it!

When should you use RIGHT JOIN?

In practice, RIGHT JOIN is used less often than LEFT JOIN. But there are situations where it can be handy:

• If the right table is more important (like, it’s your main table);
• When you want to write your query in an order where the right table logically comes after the left one;
• If you’re working with existing code that always uses RIGHT JOIN by convention.

Visualizing RIGHT JOIN

To get a better feel for RIGHT JOIN, let’s look at a diagram:

Left table            Right table
   [ A ] --------> [ 1 ] 
   [ B ] --------> [ 2 ]
   [ C ] --------> [ 3 ]
   [ D ]           [ 4 ]

When you use RIGHT JOIN, you get all the values from the right table, along with any matching data from the left table. Anything missing from the left table will be filled in with NULL.

Practice Task

Let’s check your skills in practice. Here’s a task:

You have tables departments with departments and employees with employees: departments:

employees:

Write a query using RIGHT JOIN that returns a list of all departments, including employees if they’re in a department, or NULL if there are no employees.

Try to solve it yourself before peeking at the answer.

Answer to the Task

Here’s what the query will look like:

SELECT
    departments.department_name,
    employees.name AS employee_name
FROM employees RIGHT JOIN departments
    ON employees.department_id = departments.department_id;

The result will be:

Common Mistakes When Using RIGHT JOIN

When working with RIGHT JOIN, beginners often run into these issues:

1. Forgetting the ON join condition. If you don’t specify the condition, you’ll get a cartesian product of the tables, which can be a total mess.
2. Mixing up RIGHT JOIN and LEFT JOIN. If you use the tables in the wrong order, your result will be wrong.
3. Not handling NULL values. Queries with RIGHT JOIN often produce NULLs, which you need to interpret correctly or replace using COALESCE().

That’s it! Now you know how to use RIGHT JOIN, understand the difference between RIGHT JOIN and LEFT JOIN, and can pick the right type of join for your tasks. In the next lecture, we’ll talk more about FULL OUTER JOIN, where you join everything-everything from both tables. Good luck with your queries!