Joins

Two tables walk into a query…

▶ the lesson · narrated sketch video · tap for sound

the intro

A JOIN combines rows from two tables using a matching column. INNER JOIN keeps only matches, LEFT JOIN keeps every row on the left even without a match. Joins are how normalized data becomes useful answers — and the number one interview topic in SQL.

fun fact ✦

There is even a “self join” — a table joined to itself. Org charts and comment threads run on it.

the docs

the deep dive

the ideas behind the video — concepts, diagrams, a walkthrough and the traps to avoid

Normalization deliberately scattered your data across tables; the JOIN is how you stitch it back together at query time. The real skill is not memorizing join flavors — it is predicting, before you run anything, roughly how many rows the answer should hold. If you know the expected row count, every join type is just syntax.

know the row count before you run

ordersusersJOIN
two tables meet on one shared key

key terms

join key

The shared column the match is made on. Usually a primary key meeting a foreign key.

ON o.user_id = u.id

INNER JOIN

Keep only the rows that match in both tables.

orders that have a user

LEFT JOIN

Keep every left row; fill the right side with NULL when there is no match.

users with zero orders

cross join

Every row paired with every row. Usually a missing ON clause, occasionally exactly what you need.

sizes x colors = every SKU

how it works

  1. 1
    Name your tables

    FROM users u gives you a short alias, so every column reference stays readable.

  2. 2
    Find the shared key

    Orders carry user_id; users carry id. The join condition connects exactly those two.

  3. 3
    Pick the join type

    Need only matches? INNER. Need every left row, gaps included? LEFT.

  4. 4
    Check the row count

    A join can duplicate rows before you aggregate. Count the right column or the totals will inflate.

the code, explained

every user with their order count — zeros included

1SELECT u.name, count(o.id) AS orders
ignores NULLs: counts real orders only
2FROM users u
3LEFT JOIN orders o ON o.user_id = u.id
keeps users with zero orders in the result
4GROUP BY u.name
5ORDER BY orders DESC;

side by side

INNER JOIN

  • only rows matching both sides
  • missing pairs silently vanish
  • counts can shrink surprisingly
VS

LEFT JOIN

  • every left row survives
  • missing right becomes NULL
  • count zeros: users with 0 orders
orders rowuser_idusers row
the left key looks up its partner row on the right
🪤 gotcha

A join can multiply rows before you aggregate. One user with five orders turns users JOIN orders into five rows, so count(*) counts orders, not users. Count the far table id, or aggregate in a subquery first.

quick answers

How many joins is too many?

When you can no longer predict the row count. Five well-understood joins beat one scary number every time.

Why did my COUNT get bigger after a join?

Fan-out: a user with five orders appears five times. Count the far-side id, or aggregate per user in a subquery first.

Do joins scale?

Yes, with indexes on the join keys. Postgres hashes and merges millions of rows happily; missing indexes are what make joins feel slow.

quick hits about Joins

short answers · interviews, tips, trivia & hidden gems

🎤 interview

INNER vs LEFT JOIN?watch ↗

📚 tutorial

A join, visualizedwatch ↗

💎 deep

A table high-fiving itselfwatch ↗

💎 deep

The join everyone calls a bugwatch ↗