Learn System Design Inner vs Outer Join

SQL

INNER JOIN vs OUTER JOIN

An inner join keeps only the rows that match on both sides. An outer join keeps everything else too — toggle below and watch the result set change.

4 join types 2 sample tables ~3 min

See it happen

Which rows actually come back?

Two small tables, one shared key. Pick a join type and watch the source rows light up and the result table update.

customers

id name
1 Ava
2 Ben
3 Cleo
4 Drew
5 Farah

orders

id customerId item
101 1 Headphones
102 2 Mug
103 2 Notebook
104 6 Charger
105 3 Backpack

Result

customer item

The short version

Inner vs outer, in one scan

INNER — only what matches

  • Returns rows that have a matching key on both sides.
  • Drops unmatched rows from either table entirely.
  • The smallest possible result set for a given pair of tables.

OUTER — everything, matched or not

  • LEFT keeps every row from the first table.
  • RIGHT keeps every row from the second table.
  • FULL keeps every row from both — the union.

Test yourself

Which join is it?

Answer from the tables above. Nothing is saved or sent anywhere.

Question 1 of 3 Score: 0
Farah has no orders. Which join(s) still include her, with her item shown as NULL?

Keep going

More system design breakdowns, built the same way.

System Design → ← All resources