SQL 03 — Joins

Every screen in every real app — your bank statement, a Swiggy order, the SplitEase balances page — is data from several tables stitched back together. Joins are that stitching, and they are the single most important skill in this track: get them, and SQL opens up; fake them, and every report you ever write is quietly wrong in ways nobody catches until money is involved.

The Goal

By the end of this module you can:

• Explain why data is split across tables at all — one fact, one place — and what goes wrong when it isn’t
• Run the row-matching machine in your head: for each left row, which right rows does the ON predicate accept?
• Choose INNER vs LEFT deliberately, and write the canonical “who never paid” query with WHERE ... IS NULL
• Spot the classic bug where a WHERE filter silently turns a LEFT JOIN back into an INNER
• Walk two hops through a junction table without losing the thread
• Predict row multiplication on 1:N joins — and explain why a COUNT after a join can lie

The Lesson

Why the data is split at all

Suppose expense stored the payer’s name instead of paid_by:

Now Asha corrects the spelling of her name. You must update three rows — miss one and the database holds two truths about the same person. That’s an update anomaly, and it has siblings: you can’t record a friend until they’ve paid for something (insertion anomaly), and deleting someone’s last expense erases the person (deletion anomaly).

The cure is the relational model’s core rule: one fact lives in one place. Asha’s name lives in exactly one friend row; every expense stores only her id. Rename her once, every expense agrees instantly. The full discipline behind this — normalization, designed from requirements — is module 05. The price of splitting is that reading the data back means re-joining it. This module is that price, and it’s cheaper than you think.

A join is a row-matching machine

Conceptually, this is the whole thing:

For each row of the left table, scan the right table; for every right row where the ON predicate is TRUE, emit one combined row.

SELECT f.name, e.description, e.amount_paise
FROM friend f
JOIN expense e ON e.paid_by = f.id;

The ON clause is just a predicate — the same TRUE/FALSE/NULL machinery as module 02’s WHERE, but deciding matches instead of survival. Asha’s row meets the dinner row: e.paid_by = f.id → 1 = 1 → TRUE → combined row out. Asha’s row meets the cab row: 2 = 1 → FALSE → nothing.

(One planner honesty note: that mental loop is a real strategy called a nested loop, but Postgres may pick a hash join or merge join instead — same result, different speed. Module 06 shows you which one ran. The mental loop is always correct for predicting the rows.)

INNER JOIN — only matches survive

Plain JOIN means INNER JOIN. Rows that find no partner — on either side — simply don’t appear:

SELECT f.name, e.description
FROM friend f
INNER JOIN expense e ON e.paid_by = f.id;

On our seed data: nine combined rows… no — count it properly. Asha paid 3 times, Rohit 2, Darshan 1, Meera 0. Result: 6 rows. Asha appears three times (more on that multiplication below). And Meera? She paid for nothing, matched nothing, and is gone — not NULL, not flagged, absent. INNER JOIN doesn’t report non-matches; it erases them. If your question is “show me expenses with payer names,” that’s perfect. If your question involves Meera, you need the next join.

LEFT JOIN — every left row survives

SELECT f.name, e.description
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id;

LEFT JOIN makes one promise: every row of the left table appears at least once. Friends with matches behave exactly like INNER. Meera, with no match, gets one row anyway — and every expense column in it is filled with NULL. That NULL is not an error or missing data; it is the join telling you “no match existed.” It’s manufactured information.

Which sets up the canonical query — one of the most-asked patterns in interviews and real work, “which friends have never paid for anything”:

SELECT f.name
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
WHERE e.id IS NULL;

Read it as a machine: LEFT JOIN guarantees everyone appears; matched friends carry a real e.id; unmatched friends carry NULL there; the WHERE keeps only the NULLs. Result: Meera. This LEFT JOIN ... WHERE right.id IS NULL shape is a tool you’ll reuse for the rest of your career — unused coupons, products never ordered, users who never logged in.

RIGHT JOIN and FULL JOIN — the honest short version

RIGHT JOIN is a LEFT JOIN written backwards: every right row survives. Nobody needs it — A RIGHT JOIN B is exactly B LEFT JOIN A, and almost every team reorders the tables and uses LEFT for consistency. Know it exists, recognize it in old code, move on.

FULL JOIN keeps unmatched rows from both sides, NULL-padding whichever side is missing. It’s genuinely rare — mostly reconciliation jobs (“rows in the bank file but not in ours, and vice versa, in one query”). If you reach for FULL JOIN twice in a month, check your schema.

ON vs WHERE — the bug that turns LEFT into INNER

This is the classic outer-join mistake, and it produces plausible wrong answers, which is the worst kind. Intent: “show every friend, with their ₹1000+ expenses if any.”

SELECT f.name, e.description
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
WHERE e.amount_paise >= 100000;        -- the bug

Run the machine. The LEFT JOIN dutifully produces a row for Meera with e.amount_paise = NULL. Then WHERE evaluates NULL >= 100000 → NULL → row dropped. Rohit’s expenses (cab ₹600, petrol ₹900) are all under the bar, so both his rows fail the filter too — Rohit vanishes entirely. Your “every friend” query now returns Asha and Darshan. A WHERE condition on right-table columns silently undoes the LEFT JOIN, because the NULL-padded rows can never pass it.

The fix: filters that decide which right rows count as matches belong in ON:

SELECT f.name, e.description
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
                   AND e.amount_paise >= 100000;

Now the predicate runs during matching. Rohit and Meera match nothing — and LEFT JOIN’s promise kicks in: they still appear, NULL-padded. All four friends, as intended.

The rule to keep: on an outer join, ON decides matching; WHERE decides survival of the combined row. On an INNER join the two are interchangeable — which is exactly why people pick up the sloppy habit and only get burned when they graduate to LEFT. (One legitimate exception: WHERE e.id IS NULL works because it targets the NULL-padding itself.)

Junction tables — joining in two hops

friend and expense_group are many-to-many: Rohit is in several groups, every group has several friends. SQL has no list column — the relationship gets its own table, where each row is one membership fact:

CREATE TABLE expense_group (          -- "group" alone is a reserved word in SQL
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE group_member (
  group_id  BIGINT NOT NULL REFERENCES expense_group(id),
  friend_id BIGINT NOT NULL REFERENCES friend(id),
  PRIMARY KEY (group_id, friend_id)   -- a junction table IS its two FKs
);

(The full SplitEase schema is designed properly from requirements in module 05 — we’re creating these two tables early because joins are unteachable without them. The DDL is identical to what 05 lands on.)

Getting from a friend to their groups takes two hops — friend to membership, membership to group:

flowchart LR
    F[friend] -->|"id = friend_id"| GM[group member]
    GM -->|"group_id = id"| G[expense group]

SELECT f.name AS member, g.name AS group_name
FROM friend f
JOIN group_member gm ON gm.friend_id = f.id
JOIN expense_group g ON g.id = gm.group_id;

Each JOIN is the same machine, run twice; the junction row is the wire connecting the two ends. This is also exactly what Hibernate generated for @ManyToMany in Spring Boot 04 — that expense_sharers table in your DDL log was a junction table, and now you can query it by hand.

Self-joins, briefly

A table can join to itself — you just alias it twice and treat the aliases as two tables. “Pairs of expenses paid by the same friend”:

SELECT a.description, b.description, f.name
FROM expense a
JOIN expense b ON b.paid_by = a.paid_by AND a.id < b.id
JOIN friend f ON f.id = a.paid_by;

The a.id < b.id trick keeps each pair once and kills self-pairs. Self-joins show up in interviews (“employees earning more than their manager”) and in any hierarchy stored as a parent_id column. File the pattern; don’t drill it yet.

Row multiplication — why COUNT after a join can lie

Look back at the first INNER join: friend has 4 rows, the result has 6 — Asha appears three times because joining 1:N copies the 1 side once per match. The result’s grain changed: it’s no longer “one row per friend,” it’s “one row per expense.”

Now the lie:

SELECT count(*)
FROM friend f
JOIN expense e ON e.paid_by = f.id;    -- → 6, not 4, not 3

Six what? Not friends, not “friends who paid” — six friend-expense matches. Anyone who reads that as a friend count just reported a wrong number with full confidence. Joins change what a row means; every COUNT, SUM, and AVG downstream inherits the new meaning. Aggregating multiplied rows correctly is precisely what module 04’s GROUP BY exists for.

And the smell from module 02 comes home: when someone “fixes” duplicate output by adding SELECT DISTINCT, they’re usually hiding multiplication they don’t understand. DISTINCT compares whole output rows — the moment two genuinely different facts produce identical output values, DISTINCT merges them and the numbers go subtly wrong. The honest fix is always the same: ask what should one row of this result mean?, then make the join produce exactly that grain.

What Hibernate does with all of this

Every @ManyToOne in SplitEase is a join Hibernate writes on your behalf — expense.payer in Java is JOIN friend ON friend.id = expense.payer_id in the log. And the N+1 problem from Spring Boot 04 is, in this module’s terms, Hibernate choosing N+1 separate queries where one JOIN would do — one SELECT for expenses, then one per payer, instead of the single INNER JOIN you wrote in this lesson by hand. join fetch is you ordering it to use the join.

This is the payoff of the whole track so far: with spring.jpa.show-sql=true on, you can now read the difference in the log — many small SELECTs versus one JOIN — instead of taking the framework’s word for it.

See It Move

Watch the matching machine run one row at a time — the pointer walks the left table, the ON predicate accepts or rejects, and the result table grows. Toggle INNER vs LEFT and run it again.

Step through it and notice:

• In INNER mode, the friend with no expenses is simply skipped — no row, no trace, erased.
• Flip to LEFT: the same friend now lands in the result with NULLs on the right side. That NULL row is the answer to “who never paid” — it’s what WHERE e.id IS NULL catches.
• Watch a friend with multiple expenses get emitted once per match — row multiplication happening in front of you, the reason COUNT changes meaning after a join.

Check The Concept

How This Shows Up At Work

• The dashboard that tripled revenue. Someone joins expenses to their shares to “include the split info,” then SUMs amount_paise — every 3-way expense now counts three times. Finance celebrates, then audits. The fix is understanding grain; the person who asks “what does one row mean after this join?” finds it in minutes.
• The churn report that lied for a quarter. “All customers with their recent orders” written as LEFT JOIN with the date filter in WHERE — every customer without recent orders (the exact people the churn team needed) silently dropped. Classic ON-vs-WHERE; nobody notices because the output looks normal.
• The N+1 in code review. A PR loops over findAll() calling getPayer().getName(). You’ve now hand-written the one JOIN that replaces those N+1 queries, so your review comment can include the actual SQL — that specificity is a seniority signal.
• The interview pair. “Difference between INNER and LEFT JOIN?” followed by “find customers who never ordered” — the second is literally the LEFT JOIN ... IS NULL pattern from this module, and it’s on nearly every SQL screen (LeetCode keeps it as “Customers Who Never Order” for a reason).
• The cross-join outage. A reporting query loses its join condition in a refactor, two million-row tables multiply, and the report server runs out of memory at month-end. Whoever recognizes “row count = m × n” names the bug from the symptom alone.

Build This

You’re continuing on splitease_sql with module 02’s seed (4 friends, 6 expenses — rerun its step 1 block if your data drifted).

1. INNER join — expenses with payer names. Type it, count the rows before you run it:

SELECT e.description, e.amount_paise, f.name AS paid_by
FROM expense e
JOIN friend f ON f.id = e.paid_by
ORDER BY e.id;

Six rows; Asha appears three times. Where’s Meera? Say the answer out loud before moving on.

2. LEFT join — who never paid.

SELECT f.name
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
WHERE e.id IS NULL;

One row: Meera. Now delete the WHERE line and rerun — find Meera’s NULL-padded row in the full output so you’ve seen the thing the WHERE catches.

3. Watch LEFT silently become INNER. Intent: every friend, with their ₹1000+ expenses if any.

SELECT f.name, e.description
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id
WHERE e.amount_paise >= 100000;

Count the names: Asha and Darshan only — Rohit and Meera have vanished from an “every friend” query. Now move the filter into the ON:

SELECT f.name, e.description
FROM friend f
LEFT JOIN expense e ON e.paid_by = f.id AND e.amount_paise >= 100000;

All four friends are back; Rohit and Meera carry NULL descriptions. This pair of runs is the most valuable thing in this module — you watched the bug happen.

4. Build the junction and walk two hops. Create and seed:

CREATE TABLE expense_group (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE group_member (
  group_id  BIGINT NOT NULL REFERENCES expense_group(id),
  friend_id BIGINT NOT NULL REFERENCES friend(id),
  PRIMARY KEY (group_id, friend_id)
);

INSERT INTO expense_group (name) VALUES ('Goa Trip'), ('Flat 402');
INSERT INTO group_member VALUES (1, 1), (1, 2), (1, 4), (2, 2), (2, 3);

Then the two-hop join:

SELECT g.name AS group_name, f.name AS member
FROM expense_group g
JOIN group_member gm ON gm.group_id = g.id
JOIN friend f ON f.id = gm.friend_id
ORDER BY g.name, f.name;

 group_name | member
------------+---------
 Flat 402   | Darshan
 Flat 402   | Rohit
 Goa Trip   | Asha
 Goa Trip   | Meera
 Goa Trip   | Rohit
(5 rows)

Five rows — five membership facts. Rohit appears under both groups because he holds two junction rows.

5. Break it on purpose #1 — the cross-join explosion. Old-style comma join with the condition “forgotten”:

SELECT count(*) FROM friend f, expense e;

24 — every friend paired with every expense, 4 × 6. Now imagine both tables at a million rows: a trillion-row result. This is why modern JOIN ... ON syntax won: forgetting ON is a syntax error, not a silent explosion. Add WHERE e.paid_by = f.id and watch it drop back to 6.

6. Break it on purpose #2 — ambiguous column.

SELECT id, name, description
FROM friend f
JOIN expense e ON e.paid_by = f.id;

ERROR:  column reference "id" is ambiguous

Both tables have an id; Postgres refuses to guess. Qualify it — f.id — and it runs. From today, qualify every column in every join, even unambiguous ones: the error is the kind version of this problem; the cruel version is a query that picks the column you didn’t mean.

Where to Practice

Check Yourself

1. Why doesn’t expense just store the payer’s name as text? Name the anomaly that design causes.
2. State the row-matching machine for A JOIN B ON p in one sentence.
3. What does a NULL-padded row in a LEFT JOIN result mean, and what query shape turns it into “find the non-matches”?
4. Why does a WHERE filter on right-table columns break a LEFT JOIN, and where does the filter belong instead?
5. What is a junction table, and why is its primary key the pair of foreign keys?
6. friend joins expense 1:N. What happens to each friend row, and why can a COUNT over the result mislead?
7. When is SELECT DISTINCT on a join result a smell, and what’s the honest fix?
8. In join terms, what is Hibernate’s N+1 problem?

Explain it out loud: Explain to an empty chair why the “every friend with their big expenses” query lost Rohit and Meera when the filter sat in WHERE, and got them back when it moved to ON — walking the NULL-padded row through both versions. If you can’t narrate what happens to Meera’s row at each step, rerun Build This step 3.

Still Unclear?

Give me two tiny tables (4 rows and 5 rows) as text. Make me hand-execute
an INNER JOIN and then a LEFT JOIN on paper, row by row, writing the output
myself. Check my answer, then change the ON predicate and make me do it
again. Do not show me the result before I commit to mine.

Explain why moving a condition between ON and WHERE changes nothing on an
INNER JOIN but changes everything on a LEFT JOIN. Then give me three
realistic LEFT JOIN queries with the filter in the wrong place and make me
predict exactly which rows vanish before you confirm.

I understand joins individually. Quiz me on grain: give me five join queries
over a friends/expenses/shares schema and for each one make me state what
ONE ROW of the result means before I am allowed to aggregate anything.

Why AI Can’t Do This For You

AI writes syntactically perfect joins on request — that was never the hard part. The hard part is knowing whether the 6 rows that came back should have been 4, and that requires knowing your data’s grain, your schema’s cardinalities, and what the business question actually asks. The ON-vs-WHERE bug returns plausible data; the multiplied COUNT returns a confident number. Nothing about the output says “wrong.” Only the person who runs the matching machine in their head catches it.

And when Hibernate fires 201 queries where one JOIN would do, AI can’t see your SQL log — you can. This module is the moment JPA stops being a faith-based system: you’ve now written by hand the exact join Hibernate was supposed to write for you, which means you can finally judge its work.

Module done? Add it to today’s tracker