Full lesson
Explore the full explanation, examples, and visuals at your own pace.
Two customers. Three orders. Which rows pair up?
One customer can produce two joined rows. C1 matches both O10 and O11, while C2 has no order. O12 points to missing customer C3, so this example assumes no enforced foreign key prevents that order from existing.
INNER JOIN keeps matched pairs
The ON condition matches rows with equal customer IDs. C1 appears twice because two orders reference it. INNER JOIN drops C2, which has no order, and O12, which has no matching customer.
SELECT c.id AS customer, o.id AS order_id
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id;
-- Illustrative rows, order unspecified:
-- C1 O10
-- C1 O11LEFT JOIN keeps unmatched customers too
Because customers is the left table, LEFT JOIN keeps every customer: C1 pairs with both orders, while C2 gets a NULL order_id. O12 has no matching customer, so it doesn’t appear.
SELECT c.id AS customer, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.id = o.customer_id;
-- Illustrative rows, order unspecified:
-- C1 O10
-- C1 O11
-- C2 NULLIn this example, which result row does LEFT JOIN include that INNER JOIN does not?
Let's think this through. In this example, which result row does LEFT JOIN include that INNER JOIN does not? A: C2 with no order. B: O12 with no customer. C: C1 with O11. Choose an answer, or just think it through. I'll explain in a moment.
- C2 with no order
- O12 with no customer
- C1 with O11
In this example, which result row does LEFT JOIN include that INNER JOIN does not?
The answer is A: C2 with no order. Customers is the left table, so C2 survives even though it has no matching order. Its order columns are NULL. O12 is unmatched on the right, so LEFT JOIN does not retain it.
- C2 with no order
- O12 with no customer
- C1 with O11
The unmatched order is the final difference
Joins pair rows with matching keys; the join type decides which unmatched rows survive. LEFT keeps C2 with no order, while FULL OUTER also keeps O12 with no customer.
FULL OUTER JOIN keeps both sides
The result has four rows: C1 pairs with both O10 and O11, while C2 has no order, so its order ID is NULL. O12 has no matching customer, so customer is NULL. Their display order isn’t guaranteed.
SELECT c.id AS customer, o.id AS order_id
FROM customers AS c
FULL OUTER JOIN orders AS o ON c.id = o.customer_id;
-- Illustrative rows, order unspecified:
-- C1 O10
-- C1 O11
-- C2 NULL
-- NULL O12Choose by required rows
Choose by which rows must survive: INNER keeps matches, LEFT keeps every customer, and FULL OUTER keeps unmatched rows on either side. NULL marks a missing partner; C1 can appear twice.
- INNER: matched pairs only
- LEFT: every customer
- FULL OUTER: unmatched rows from either side
Match first. Then keep the required unmatched rows.
Match first. Then keep the required unmatched rows. LEFT preserves C2 without an order; FULL OUTER also preserves O12 without a customer.