join

RIGHT JOIN: Keeping All Rows From the Right Table

Use RIGHT JOIN to list every department — including ones with no employees — alongside any matching staff.

Overview

A RIGHT JOIN returns every row from the right table and the matched rows from the left table. If a left-table row has no match, you still get the right-table row with NULLs for the left side. It's the mirror image of LEFT JOIN and is handy when you want to guarantee the right table's rows all appear — for instance, showing every department even if some have no staff assigned in this dataset.

The Query

Employees & Departments

Loading database engine...

Step-by-Step Breakdown

FROM employees e RIGHT JOIN departments d — departments is the right table, so every department row is kept.
ON e.department_id = d.id — matches employees to their department.
Because every department in this dataset does have employees, no NULL employee columns appear — but the query would still show a department with empty employee columns if one had none.

RIGHT vs LEFT

A RIGHT JOIN B is equivalent to B LEFT JOIN A. Most teams standardize on LEFT JOIN and flip the table order, so you'll see RIGHT JOIN less often in real codebases.

Variations

Count employees per department, keeping departments with zero staff visible:

Employees & Departments

Loading database engine...

Common Mistakes

Confusing which side is preserved. With RIGHT JOIN, the table after RIGHT JOIN is the one that's fully kept — not the first one listed.
Filtering the right table in WHERE. Adding WHERE e.first_name IS NOT NULL after a RIGHT JOIN silently converts it into an inner join by dropping the unmatched right rows.
Assuming symmetry with INNER JOIN. Unlike INNER JOIN, swapping the table order with RIGHT/LEFT changes which unmatched rows survive.