SQL RIGHT JOIN

RIGHT JOIN is the mirror image of LEFT JOIN: it keeps every row of the right table and fills the left table’s columns with NULL where nothing matches. Anything a right join does, a left join with the tables swapped does too, which is why you will see it less often, but you must be able to read it.

RIGHT JOIN syntax

SELECT columnsFROM left_table lRIGHT JOIN right_table r ON r.key = l.key;

Sample data

Every department, with its employees

SELECT d.dept_name, e.first_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idORDER BY d.dept_id, e.first_name;

Departments with no employees

SELECT d.dept_id, d.dept_nameFROM employees eRIGHT JOIN departments d ON d.dept_id = e.dept_idWHERE e.emp_id IS NULL;

Rewrite as a LEFT JOIN

Swap the tables and change RIGHT to LEFT. The result is identical:

SELECT d.dept_name, e.first_nameFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idORDER BY d.dept_id, e.first_name;

When RIGHT JOIN reads naturally

In a long chain of inner joins, you may want to keep all rows of a table you join last. A right join at the end does that without restructuring the query:

SELECT p.product_name, o.order_id, o.statusFROM orders oJOIN order_items oi ON oi.order_id = o.order_idRIGHT JOIN products p ON p.product_id = oi.product_idWHERE p.category = 'Furniture'ORDER BY p.product_name, o.order_id;

SELECT p.product_name, oi.order_idFROM order_items oiRIGHT JOIN products p ON p.product_id = oi.product_idORDER BY p.product_id, oi.order_id;

RIGHT JOIN FAQ