30 RIGHT and FULL JOIN

RIGHT JOIN mirrors LEFT JOIN: every row from the right side is kept, even if the left side has no match. FULL OUTER JOIN keeps everything from both sides, filling unmatched columns with NULL. In practice, LEFT JOIN is far more common than RIGHT JOIN — it usually reads more naturally to put the “primary” table first and use LEFT JOIN to bring in supplemental data. FULL JOIN shines when you need to see matches and both kinds of orphans at once (e.g. reconciliation reports).

What you’ll learn

• RIGHT JOIN: keep everything on the right
• FULL JOIN: keep everything on both sides
• Why LEFT JOIN usually wins on readability
• Detecting unmatched rows: WHERE other.id IS NULL
• Ordering when both sides may be NULL

Example

-- Uses customers_example and orders_example from the previous lesson
-- (run inner-and-left-join.sql first if you have not)

-- RIGHT JOIN: every order, even those without a matching customer
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
RIGHT JOIN orders_example o ON o.customer_id = c.id
ORDER BY o.id;

-- FULL OUTER JOIN: every customer and every order
SELECT c.name, o.id AS order_id, o.total
FROM customers_example c
FULL OUTER JOIN orders_example o ON o.customer_id = c.id
ORDER BY c.id NULLS LAST, o.id NULLS LAST;

-- "Orphans" — find unmatched rows on either side
-- Customers with no orders:
SELECT c.id, c.name
FROM customers_example c
LEFT JOIN orders_example o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Orders with no customer:
SELECT o.id, o.customer_id
FROM customers_example c
RIGHT JOIN orders_example o ON o.customer_id = c.id
WHERE c.id IS NULL;

RIGHT JOIN A ON ... is exactly LEFT JOIN A with the tables swapped — A LEFT JOIN B ON cond is the same as B RIGHT JOIN A ON cond. Most style guides recommend rewriting RIGHT JOIN to LEFT JOIN so the “primary” table appears first in the FROM clause.

FULL OUTER JOIN is the symmetric counterpart: every row from both sides appears, and unmatched columns are NULL. Order 3 (customer_id = 99) appears with NULL name; Bob and Carol appear with NULL order columns. The ORDER BY ... NULLS LAST keeps the result readable.

The classic anti-join pattern — “rows in A that have no match in B” — uses LEFT JOIN ... WHERE B.id IS NULL. PostgreSQL recognizes this idiom and often plans it as an efficient anti-join.

Run the example

To run (requires the tables from the previous lesson):

$ psql -f source/right-and-full-join.sql postgres
 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
       |        3 | 10.00
(3 rows)

 name  | order_id | total
-------+----------+-------
 Alice |        1 | 50.00
 Alice |        2 | 30.00
 Bob   |          |
 Carol |          |
       |        3 | 10.00
(5 rows)
 ...

Common pitfalls

• Writing RIGHT JOIN and then needing to flip the columns mentally. Swap the table order and use LEFT JOIN for readability.
• FULL JOIN results have NULLs on both sides; an ORDER BY without NULLS LAST/FIRST can scatter them confusingly.
• WHERE other.id IS NULL after a LEFT JOIN is the anti-join idiom. NOT EXISTS (...) and NOT IN (...) are alternatives — NOT EXISTS is usually the safest with NULLs.
• Joining multiple tables with FULL JOIN is rarely what you want — the cartesian-like behavior compounds. Stick to simple cases.

Downloads

SQL file: right-and-full-join.sql · All lesson files · Setup guide