Right Join
SQL · Joining Multiple Tables
Theory
RIGHT JOIN (RIGHT OUTER JOIN) is the mirror image of LEFT JOIN: it keeps every row from the right table, the one written second, and fills the left table's columns with NULL where there is no match. A RIGHT JOIN B gives the same rows as B LEFT JOIN A; only the column order in SELECT * differs.
In the example below every department appears, including Sales with no employees, while employees without a valid department are dropped. Most teams rewrite right joins as left joins by swapping the table order, because reading queries is easier when the main table always comes first. RIGHT JOIN is supported by PostgreSQL, MySQL, SQL Server, Oracle and recent SQLite versions.
Why we use it
Sometimes the table that must be kept completely appears second in an existing query. RIGHT JOIN keeps all rows of that table without reordering the whole FROM clause.
When to use it
Use RIGHT JOIN when you need every row of the second table, for example to list departments with no staff. In most code, prefer swapping the tables and using LEFT JOIN for consistency.
How it works
- Take a row from the right table (departments)
- Look for left rows that satisfy the ON condition
- Any match? Yes: One combined row per match. No: Right row once, NULLs on the left.
- Repeat for every right row
- Return all right rows
Syntax
SELECT columns
FROM t1
RIGHT JOIN t2 ON t1.id = t2.t1_id;Example
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;Key points
- Mirror image of LEFT JOIN
- A RIGHT JOIN B returns the same rows as B LEFT JOIN A
- Less common: usually rewritten as a LEFT JOIN
- Unmatched right rows get NULL in the left table's columns
- Older SQLite versions lack RIGHT JOIN; recent ones support it