# SQL RIGHT JOIN: Return Every Row From the Right Table

- URL: https://betteranalyst.com/sql/right-join

SQL guide · Joins

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

- [SQL INNER JOIN](https://betteranalyst.com/sql/inner-join)
- [SQL LEFT JOIN](https://betteranalyst.com/sql/left-join)
- [SQL FULL OUTER JOIN](https://betteranalyst.com/sql/full-outer-join)
- [SQL IS NULL](https://betteranalyst.com/sql/is-null)
- [SQL GROUP BY](https://betteranalyst.com/sql/group-by)
- [SQL FOREIGN KEY](https://betteranalyst.com/sql/foreign-key)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)