skip to today's puzzle

FULL OUTER JOIN keeps matched and unmatched rows

FULL OUTER JOIN matches related rows using ON, then also keeps unmatched rows from both tables. Missing values on either side appear as NULL. It is useful when you need a complete comparison instead of favoring one table. Not every database supports it — older MySQL versions, for example, do not.

A tiny example

In plain English: Show me customers next to orders, keeping customers with no order and orders with no customer.

SELECT customers.name AS customer, orders.id AS order_id
FROM customers
FULL OUTER JOIN orders
  ON customers.id = orders.customer_id;

Query result

customerorder_id
Amara101
SanaNULL
NULL104

Amara matches order 101, Sana matches nothing (NULL for order), and order 104 has no customer (NULL for customer).

See every JOIN type side by side in the SQL JOIN diagram & cheat sheet.

Related SQL lessons

Ready for a break?

A fresh, bite-sized SQL puzzle waits every morning. Tap tiles, see real results. No terminal, no setup, no timers.

Try today's free SQL puzzle