What is a FULL OUTER JOIN?
SQLLevel: FoundationJOINs Basics
What is a FULL OUTER JOIN?
Hints
- Write the join keys and relationship cardinality.
- What happens to duplicates and unmatched rows? Check WHERE versus ON.
Answer
A FULL OUTER JOIN returns all rows from both tables. Where there's no match on either side, NULLs fill in the missing columns.
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
This gives you: matched pairs + customers without orders (right side NULLs) + orders without customers (left side NULLs).
Note: MySQL doesn't support FULL OUTER JOIN directly. You simulate it with a LEFT JOIN UNION ALL RIGHT JOIN with NULL filter.
Source and licence: SQL Interview Questions — Arda Kocadoru · MIT