RIGHT JOIN and FULL OUTER JOIN — when and why to use them

RIGHT JOIN Is LEFT JOIN With Tables Swapped

RIGHT JOIN returns all rows from the right table, with NULL for left-side columns when no match exists. It is functionally identical to a LEFT JOIN with the tables in reversed order. Most developers avoid RIGHT JOIN for readability — always write the table you want to keep all rows from on the left.

-- These return the same result
SELECT o.id, c.name
FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;

-- Equivalent and more readable
SELECT o.id, c.name
FROM orders o LEFT JOIN customers c ON o.customer_id = c.id;

FULL OUTER JOIN: All Rows From Both Sides

FULL OUTER JOIN returns all rows from both tables. Where a row has no match on the other side, those columns are NULL:

-- All customers and all orders, matched where possible
SELECT
  COALESCE(c.name, 'No customer') AS customer,
  COALESCE(o.id::TEXT, 'No order') AS order_ref
FROM customers c
FULL OUTER JOIN orders o ON c.id = o.customer_id;

FULL OUTER JOIN is useful for data reconciliation — finding rows in either table that have no counterpart. SQLite does not support FULL OUTER JOIN natively (emulate it with UNION of LEFT JOINs).