SQL joins — the picture, not the jargon

You have two tables. Joins tell the DB which rows go together.

Setup

• users: Alice (1), Bob (2), Carol (3)
• orders: two for Alice, one for Bob, none for Carol

The four joins

INNER JOIN

Only rows where BOTH sides match. Carol disappears — she has no orders.

LEFT JOIN

All rows from LEFT table + matches from RIGHT (or NULLs). Carol appears with NULL amount.

RIGHT JOIN

Mirror of LEFT. Rare — usually you just flip the tables and use LEFT.

FULL OUTER JOIN

All rows from both sides, matched where possible. SQLite doesn't support directly (use UNION of two LEFT joins).

Mental model

Think of each table as a circle. INNER = intersection. LEFT = left circle whole. RIGHT = right circle whole. FULL = both circles.

Aggregation trick: LEFT JOIN + COALESCE

The last query is the killer real-world pattern:

• LEFT JOIN keeps Carol.
• COALESCE(SUM(...), 0) turns her NULL into 0.
• Now every user shows up in the total-spend leaderboard.

Try it

1. Add a third table products and 3-way join to show what each user bought.
2. Find users whose total spend > 200.