FULL OUTER JOIN
- SQL course
- Intermediate
- Lesson 13/115
- 4 min
Keep all rows from both tables.
FULL OUTER JOIN
FULL OUTER JOIN keeps every row from both tables. Unmatched rows come with NULL.
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;This query lists everyone, matched or not.
c.name, o.id is selected from customers c. orders o is joined on o.customer_id = c.id, keeping every row of both tables.
FULL OUTER JOIN — details
FULL OUTER JOIN combines LEFT and RIGHT JOIN. MySQL lacks it directly; you build it with UNION.
For example, a FULL OUTER JOIN of customers and orders includes both customers with no orders and orders with no customer.
Practice FULL OUTER JOIN
Join customers with orders using FULL OUTER JOIN.
Starter code
-- full join
SELECT c.name, o.id
FROM customers cSample solution
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;Expected output
name id Ayşe 1 NULL 5
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 What does FULL OUTER JOIN keep?
- Only the matching rows
- Only the left
- Only the right
- All rows from both tables Correct answer
Explanation: FULL OUTER JOIN keeps all rows from both tables.
2 Add FULL OUTER JOIN
SELECT c.name, o.id
FROM customers c
____ orders o ON o.customer_id = c.id;SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;Explanation: FULL OUTER JOIN is the right answer.
3 FULL OUTER JOIN: match the terms
| Term | Meaning |
|---|---|
FULL OUTER |
both |
LEFT |
all left |
RIGHT |
all right |
INNER |
intersection |