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
| customer | order_id |
|---|---|
| Amara | 101 |
| Sana | NULL |
| NULL | 104 |
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