SQL RIGHT JOIN

A RIGHT JOIN (also called a RIGHT OUTER JOIN) returns every row from the right-hand table in the join, plus any matching rows from the left-hand table. When a right-table row has no partner on the left, the row still appears, but every column that would have come from the left table is filled in with NULL. It’s the mirror image of LEFT JOIN: instead of guaranteeing every row from the first table listed, it guarantees every row from the second one. You reach for it whenever you need "everything in table B, whether or not it has a match in table A" — for example, every customer in your system, including the ones who have never placed an order.

Overview: How RIGHT JOIN Works

Conceptually, the database engine builds the result of a RIGHT JOIN by treating the right table as the one that must be fully represented. For every row in the right table, the engine looks for rows in the left table where the ON condition is true. If it finds one or more matches, it produces one output row per match, with columns from both tables filled in. If it finds no match at all, it still produces exactly one output row for that right-table row, but every column that belongs to the left table is set to NULL.

Under the hood, most query planners (SQLite included) don’t actually implement RIGHT JOIN as a distinct physical operation. Because a RIGHT JOIN of A and B is logically identical to a LEFT JOIN of B and A, the planner typically rewrites A RIGHT JOIN B ON ... into B LEFT JOIN A ON ... internally and reuses the same nested-loop or hash-join code path it already has for LEFT JOIN. This is worth knowing because it explains why RIGHT JOIN is comparatively rare in real codebases: many teams simply always write LEFT JOIN and reorder their tables instead, since it’s the more universally supported and more commonly read form.

Portability note: RIGHT JOIN is part of standard SQL and is supported by MySQL, PostgreSQL, and SQL Server. SQLite only added RIGHT JOIN and FULL OUTER JOIN support in version 3.39.0 (released June 2022) — earlier SQLite versions support only LEFT JOIN. If you need your SQL to run on an older SQLite build (for example, inside an older mobile app), rewrite any RIGHT JOIN as an equivalent LEFT JOIN with the table order swapped.

Syntax

SELECT column_list
FROM left_table
RIGHT JOIN right_table
    ON left_table.key_column = right_table.key_column
WHERE condition
ORDER BY column_list;

Clause	Purpose
FROM left_table	The "left" table; only its matching rows are guaranteed to appear.
RIGHT JOIN right_table	The "right" table; every row from this table appears at least once in the result.
ON condition	The join predicate that pairs rows from both tables, usually key = key.
WHERE condition	An optional filter applied after the join completes — see Common Mistakes below.
ORDER BY / LIMIT	Optional sorting and row-limiting, applied last, after everything else.

Examples

Example 1: Every customer, even ones without orders

Using the sample customers and orders tables, this query lists every order together with its customer, but because customers is on the right, every customer is guaranteed to show up at least once:

SELECT customers.customer_name, customers.country,
       orders.order_id, orders.order_date, orders.amount
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
ORDER BY customers.customer_id;

Result: 4 rows. There are 3 rows in orders, all of which reference customers 1 and 2, so those two customers each appear once per order they placed. The third customer (the one with no matching row in orders) still appears exactly once, but with NULL in order_id, order_date, and amount.

customer_name	country	order_id	order_date	amount
(customer 1)	…	…	…	…
(customer 2)	…	…	…	…
(customer 3)	…	NULL	NULL	NULL

Without the RIGHT JOIN — that is, with a plain inner JOIN — the third customer would have disappeared from the result entirely, because there’s no order row for it to match against.

Example 2: Every department, even empty ones

This example builds its own tiny schema to make the "unmatched right row" case obvious: four departments, but staff assigned to only two of them.

CREATE TABLE departments (
    dept_id INTEGER PRIMARY KEY,
    dept_name TEXT NOT NULL
);

CREATE TABLE staff (
    staff_id INTEGER PRIMARY KEY,
    staff_name TEXT NOT NULL,
    dept_id INTEGER
);

INSERT INTO departments (dept_id, dept_name) VALUES
    (1, 'Engineering'),
    (2, 'Sales'),
    (3, 'Marketing'),
    (4, 'Legal');

INSERT INTO staff (staff_id, staff_name, dept_id) VALUES
    (1, 'Amy Chen', 1),
    (2, 'Bo Martinez', 1),
    (3, 'Cy Patel', 2);

SELECT staff.staff_name, departments.dept_name
FROM staff
RIGHT JOIN departments ON staff.dept_id = departments.dept_id
ORDER BY departments.dept_id;

Result:

staff_name	dept_name
Amy Chen	Engineering
Bo Martinez	Engineering
Cy Patel	Sales
NULL	Marketing
NULL	Legal

Marketing and Legal have zero staff rows, but because departments is on the right, both still appear, with NULL standing in for staff_name.

Example 3: Finding unmatched rows (an anti-join)

A very common use of RIGHT JOIN is to find rows in the right table that have no counterpart on the left, by adding a WHERE ... IS NULL filter on a left-table column:

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

Result: 1 row — the one customer who has never placed an order, with order_id shown as NULL. This pattern (join, then filter on IS NULL) is the standard way to answer "which rows in B have nothing in A" questions.

How It Works Step by Step (Under the Hood)

SQL’s logical processing order applies to RIGHT JOIN just as it does to any other query: FROM/JOIN is evaluated first (including the ON condition, which decides matches and NULL-padding), then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT. The critical detail is that the outer-join padding happens during the FROM/JOIN step, before WHERE ever runs. That means a condition in WHERE that references a left-table column can silently discard the very unmatched rows the RIGHT JOIN was meant to preserve, because comparing NULL to anything (other than IS NULL) evaluates to unknown, not true.

Because RIGHT JOIN is just LEFT JOIN with the tables reversed, the two are always interchangeable. Example 1 above can be rewritten with the tables swapped and LEFT JOIN instead, producing the exact same result set:

SELECT customers.customer_name, orders.order_id, orders.amount
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
ORDER BY customers.customer_id;

Result: the same 4 logical rows as Example 1 — two customers matched to their order rows, and the third customer with NULL order columns. Many style guides prefer this LEFT JOIN form for readability, since "list of A, LEFT JOIN in B" reads more naturally than a RIGHT JOIN buried in the middle of a longer query.

Common Mistakes

Mistake 1: Filtering on a left-table column in WHERE defeats the outer join

SELECT customers.customer_name, orders.amount
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.amount > 100;

This runs without error, but it quietly breaks the point of the RIGHT JOIN. The customer with no orders has orders.amount = NULL, and NULL > 100 is neither true nor false — it’s unknown — so that row is dropped by the WHERE clause. The result ends up identical to what a plain inner JOIN would have produced. The fix is to move the condition into the ON clause, so it only restricts which right-table rows count as a match, without removing right-table rows that fail to match at all:

SELECT customers.customer_name, orders.amount
FROM orders
RIGHT JOIN customers
    ON orders.customer_id = customers.customer_id AND orders.amount > 100
ORDER BY customers.customer_id;

Result: every customer still appears at least once; customers with no order over 100 (including the one with no orders at all) show NULL for amount instead of being removed from the result.

Mistake 2: Ambiguous column names

Both orders and customers have a column named customer_id. Referencing it without qualifying which table it comes from fails:

SELECT customer_id, customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

This raises an "ambiguous column name: customer_id" error, because the engine has no way to know whether you mean orders.customer_id or customers.customer_id. The fix is to always qualify column names that exist in more than one joined table:

SELECT customers.customer_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id
ORDER BY customers.customer_id;

Result: runs cleanly and returns one row per matched order plus one row for the unmatched customer, each with an unambiguous customer_id and customer_name.

Best Practices

• Prefer rewriting RIGHT JOIN as an equivalent LEFT JOIN with the tables swapped — it’s more widely understood, reads more naturally in most codebases, and works on database versions that predate RIGHT JOIN support.
• Always qualify column names (table.column) in any query with more than one table, even when nothing is ambiguous yet — it prevents the query from breaking later when a column is added to another joined table.
• Put conditions that should only restrict matches into the ON clause; put conditions that should filter the final result (including rows you’re fine losing) into WHERE.
• Use the "join then filter on IS NULL" pattern to find rows in one table with no counterpart in another — it’s clearer and usually faster than a correlated subquery.
• Add an index on the columns used in the ON condition, especially on the larger table, so the engine can find matches without scanning the whole table for every row.
• Be explicit: always write RIGHT JOIN (or LEFT JOIN) rather than relying on old-style comma joins with filter conditions, which are easy to misread as inner joins.

Practice Exercises

• Using the customers and orders tables, write a RIGHT JOIN that lists every order alongside its customer’s name and country, making sure every customer is guaranteed to appear even without an order.
• Take the query you wrote above and rewrite it as an equivalent LEFT JOIN by swapping the table order. Confirm mentally that the result set would be identical.
• Write a RIGHT JOIN between orders and customers that returns only the customer or customers who have never placed an order (hint: filter on the order’s key column being NULL after the join).

Summary

• RIGHT JOIN keeps every row from the right-hand table, filling in NULL for left-table columns when there’s no match.
• A RIGHT JOIN B is always equivalent to B LEFT JOIN A, which is why many teams write only LEFT JOIN and reorder their tables instead.
• SQLite only supports RIGHT JOIN from version 3.39.0 onward; MySQL, PostgreSQL, and SQL Server have always supported it.
• Filtering on a left-table column in WHERE can silently turn a RIGHT JOIN into an inner join — put such conditions in ON if you want to preserve unmatched rows.
• Always qualify column names that exist in more than one joined table to avoid ambiguous-column errors.
• Join, then filter with IS NULL, is the standard pattern for finding rows with no match in the other table.