SQL guide · Joins

SQL RIGHT JOIN: Return Every Row From the Right Table

A RIGHT JOIN (or RIGHT OUTER JOIN) is the mirror image of a left join. It returns every row from the table on the right side of the join, together with the matching rows from the table on the left. If a right row has no partner, the query still returns it and fills the left-table columns with NULL.

In practice right joins are used less often than left joins, because most people read a query from top to bottom and prefer to list the main table first. Any right join can be rewritten as a left join by swapping the order of the two tables, and the two forms return exactly the same rows. Many style guides therefore ask for left joins only, to keep queries consistent.

There are still good reasons to use a right join. When you extend an existing query by adding a table at the end of a long chain, a right join can keep the new table fully preserved without rearranging the earlier joins. It is also common in generated SQL and in older reports, so you need to be able to read it.

Basic syntax

SELECT t1.column_a, t2.column_b
FROM table1 AS t1
RIGHT JOIN table2 AS t2
  ON t1.key_column = t2.key_column
[WHERE condition];

The table after RIGHT JOIN is the preserved table. Rows from table1 that do not match show up only when they satisfy the ON condition, and otherwise the table1 columns are NULL for that output row. Swapping the tables and using LEFT JOIN gives the same result.

SQL RIGHT JOIN examples

All orders, with the customer when known

The orders table is on the right, so every order is returned. Orders whose customer record is missing, for example a guest checkout with a NULL customer key, show NULL in the customer columns.

SELECT c.customer_name, o.order_id, o.order_date
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.customer_id = o.customer_id;

Orders with no matching customer

Filtering for NULL in the left table key isolates the right-side rows that have no partner. This is useful for spotting orphan records that point to a customer which no longer exists.

SELECT o.order_id, o.customer_id
FROM customers AS c
RIGHT JOIN orders AS o
  ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

The same query written as a LEFT JOIN

Swapping the table order and using LEFT JOIN produces identical rows. Seeing both forms side by side makes it easier to convert right joins in older code into the more common style.

SELECT c.customer_name, o.order_id, o.order_date
FROM orders AS o
LEFT JOIN customers AS c
  ON c.customer_id = o.customer_id;

Products and the units sold

Here products is the preserved side, so every product is listed. Products that never sold appear with a total of 0 because COALESCE replaces the missing sum.

SELECT p.product_name, COALESCE(SUM(oi.quantity), 0) AS units_sold
FROM order_items AS oi
RIGHT JOIN products AS p
  ON p.product_id = oi.product_id
GROUP BY p.product_name;

Where SQL dialects differ

PostgreSQL: Fully supports RIGHT JOIN and RIGHT OUTER JOIN with ON or USING. The planner treats it the same as the equivalent left join.

MySQL: Supports RIGHT JOIN and RIGHT OUTER JOIN. Together with LEFT JOIN and UNION it is used to emulate FULL OUTER JOIN, which MySQL does not have.

SQL Server: Supports RIGHT JOIN and RIGHT OUTER JOIN. The legacy =* operator is no longer supported, so use the ANSI form.

SQLite: RIGHT JOIN and FULL OUTER JOIN were added in SQLite 3.39.0, released in 2022. On older versions you must rewrite the query as a LEFT JOIN with the tables swapped.

Oracle: Supports ANSI RIGHT OUTER JOIN. Older Oracle code expresses the same idea with the (+) operator on the left-table columns.

Common mistakes

  • Mixing up which table is preserved. In A RIGHT JOIN B it is B, the table written after the keyword, that keeps all of its rows.
  • Putting a filter on the left table in WHERE. Unmatched rows have NULL in the left columns, so a condition such as c.country = 'US' removes them and the join acts like an inner join.
  • Mixing right and left joins in one long query. It becomes hard to see which tables are preserved, so convert everything to one direction where you can.
  • Running the query on an old SQLite version. Before 3.39.0 the syntax raises an error, so check the version or use a left join instead.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator