SQL Reference ยท Joins and Subqueries
SQL RIGHT JOIN
Reference for SQL RIGHT JOIN (RIGHT OUTER JOIN): keep every row from the right table, how it mirrors LEFT JOIN, SQLite support from 3.39 and dialect notes.
RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of LEFT JOIN: it returns every row from the right table, paired with matching rows from the left table, and fills the left columns with NULL where nothing matches.
Anything you can write with RIGHT JOIN you can write with LEFT JOIN by swapping the two tables, which is why many style guides avoid RIGHT JOIN and keep one direction throughout a query.
SELECT d.name, e.first_name FROM departments AS d RIGHT JOIN employees AS e ON e.department_id = d.id;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
SELECT columns FROM left_table RIGHT [OUTER] JOIN right_table ON condition;
| Part | Description |
|---|---|
RIGHT [OUTER] JOIN |
The table written after JOIN is the one kept completely. OUTER is optional. |
ON condition |
Decides which left rows match each right row. |
Returns: all rows of the right table, each paired with its matching left rows, or with NULL left columns when there are none.
Dialect differences
| Dialect | Notes |
|---|---|
| SQLite | RIGHT JOIN and FULL JOIN were added in version 3.39 (2022). Older versions raise a syntax error; swap the tables and use LEFT JOIN. |
| PostgreSQL, MySQL, SQL Server, Oracle | Fully supported. |
| Oracle (old style) | a.id(+) = b.a_id marks the optional side in a WHERE clause. |
Usage notes
- Same as
LEFT JOINwith swapped tables:A RIGHT JOIN BequalsB LEFT JOIN A. - Find unmatched right rows with
WHERE left.key IS NULL, as the first example does. - A
WHEREcondition on the left table cancels the outer behavior, just as forLEFT JOIN; put it inONinstead. - Reading order: queries are easier to read when the "main" table is first, so most code prefers
LEFT JOIN. - Mixing directions in a multi-join query is confusing; pick one.
- Column order in
SELECT *follows the written table order (left table first), not which side is preserved. - Compatibility: if your code must run on older SQLite versions, avoid
RIGHT JOIN.
Related
LEFT JOIN,FULL OUTER JOIN,INNER JOIN, anti-joins- Lessons: Right and full joins, Joins