RIGHT JOIN

Definition

RIGHT JOIN (or RIGHT OUTER JOIN) keeps every row from the RIGHT table, matching in data from the left where possible, and filling NULL where there's no match — the mirror image of LEFT JOIN (16.2).

Running example — departments(id, name) and employees(id, name, department_id, manager_id, salary), where department_id/manager_id can be NULL:

departments: (1, Engineering), (2, Sales), (3, Marketing)

employees: (1, Asha, dept=1, mgr=NULL, 90000), (2, Rohan, dept=1, mgr=1, 70000), (3, Neha, dept=2, mgr=1, 65000), (4, Vikram, dept=NULL, mgr=1, 50000)

Note: Marketing (dept 3) has no employees; Vikram has no department; Asha has no manager.

sql
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

This preserves every department, including Marketing (which shows NULL for e.name, since no employee belongs to it) — while Vikram (no department) is excluded, since employees is now the non-preserved side.

Edge Cases and Pitfalls

• A RIGHT JOIN B is exactly equivalent to B LEFT JOIN A (with the column order preserved as written) — many style guides recommend always writing LEFT JOIN and simply reordering the tables, rather than mixing LEFT and RIGHT in the same codebase, purely for consistency and readability.
• RIGHT JOIN is fully standard SQL and supported everywhere LEFT JOIN is — it's not a niche or deprecated feature, just less commonly used in practice since most people default to phrasing queries as LEFT JOIN.
• The same WHERE-clause-filters-the-preserved-side pitfall from LEFT JOIN (16.2) applies identically here, just mirrored: filtering the LEFT table's column in WHERE can undo a RIGHT JOIN's purpose.

Key Takeaways

• RIGHT JOIN preserves every row from the right table — the mirror image of LEFT JOIN.
• A RIGHT JOIN B is always rewritable as B LEFT JOIN A — many teams standardize on LEFT JOIN for consistency.
• RIGHT JOIN is standard, fully-supported SQL, just less commonly reached for in practice.