DocsJoinsFULL OUTER JOIN

FULL OUTER JOIN

Joins

Concept

FULL OUTER JOIN returns all rows from both tables. When a row from either side has no match, the other side's columns are filled with NULL. It is the union of LEFT JOIN and RIGHT JOIN — no row from either table is ever lost.

FULL OUTER JOIN is useful for reconciliation and comparison tasks: finding records that exist in one table but not the other, merging data from two sources that may have partial overlap, or building complete reports where both sides matter equally.

Note that SQLite does not support FULL OUTER JOIN natively. In SQLite you can emulate it with a LEFT JOIN combined with a UNION ALL of a RIGHT JOIN (or a second LEFT JOIN with swapped tables). Most other major databases (PostgreSQL, MySQL 8+, SQL Server, Oracle) support it directly.

Syntax

-- Basic full outer join
SELECT
  c.customer_id AS customer_cid,
  c.first_name,
  o.order_id,
  o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id;

-- Find unmatched rows on either side
SELECT c.customer_id, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
   OR o.order_id IS NULL;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Reconcile products catalog with inventory records
-- (find products missing inventory and inventory for unknown products)
SELECT
  p.product_id   AS catalog_id,
  p.name         AS catalog_name,
  i.product_id   AS inventory_pid,
  i.quantity_on_hand
FROM products p
FULL OUTER JOIN inventory i ON i.product_id = p.product_id
WHERE p.product_id IS NULL   -- in inventory but not in catalog
   OR i.product_id IS NULL;  -- in catalog but no inventory record

Common Pitfalls & Tips

1FULL OUTER JOIN can produce many NULLs. Be prepared to handle them with COALESCE in downstream calculations.
2Not all databases support FULL OUTER JOIN. SQLite requires a UNION ALL workaround. Always check your target database's compatibility.
3FULL OUTER JOIN results can be large. If you only need unmatched rows from one side, a LEFT JOIN with IS NULL filter is more efficient.

Practice FULL OUTER JOIN queries