Skip to content
Tutorials Center
My learning

Topics

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.

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

Try it

SQL Employees kept even without a department ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

SQL The same result written as a LEFT JOIN ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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 JOIN with swapped tables: A RIGHT JOIN B equals B LEFT JOIN A.
  • Find unmatched right rows with WHERE left.key IS NULL, as the first example does.
  • A WHERE condition on the left table cancels the outer behavior, just as for LEFT JOIN; put it in ON instead.
  • 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.