S037 · Joins — INNER, LEFT, RIGHT, FULL, Anti-Join

Module M04: Databases & SQL · Session 37 of 130 · Track: DE 🔗

What you'll be able to do after this session

• Explain Joins to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S036 — SQL Basics — SELECT, WHERE, ORDER BY, LIMIT

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · SQL Joins Explained — Socratica — 12 minutes, uses Venn diagrams that finally make joins click.
• 🎥 Deep dive (30–60 min) · Stanford — Joins in SQL (Jennifer Widom) — university-grade treatment of inner / outer / natural / semi / anti joins.
• 🎥 Hands-on demo (10–20 min) · Mode Analytics — SQL JOIN Practice (video) — types real join queries against a realistic e-commerce dataset.
• 📖 Canonical article · PostgreSQL Docs — Joined Tables (§7.2.1) — the authoritative reference; skim once.
• 📖 Book chapter / tutorial · Mode Analytics — SQL JOIN Tutorial — interactive with editable queries.
• 📖 Engineering blog · Netflix Tech — Data Model Reference & Joins — how a data-heavy team thinks about joins across time-series and dimensional data.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Combining tables the right way.

Because we normalised (last session), facts about one thing live in one table. To answer a real business question — "which customer bought which product on which date?" — we need to re-combine the tables. That's what a JOIN does: it takes two tables and produces one wider table by matching rows on a shared key (the foreign key from S035).

The five join flavours only differ in what to do with rows that don't match. INNER = keep only matched rows. LEFT = keep everything from the left table, fill nulls where the right side is missing. RIGHT = mirror image. FULL OUTER = keep everything from both sides. ANTI-JOIN (WHERE NOT EXISTS) = keep only rows that don't match — brilliant for "customers who never ordered."

The right join to pick is a business decision, not a syntax decision. "Show all customers with their order counts (including zero)" needs LEFT JOIN + COUNT. "Show customers who placed an order" needs INNER JOIN. Reach for INNER by mistake and you'll silently hide the customers who matter most for the "why aren't they buying?" report.

The forever gotcha: if a customer has three orders and you INNER JOIN customers to orders, you get three rows for that customer, not one. Rows multiply along the join. Aggregate afterwards or use a subquery.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Tables (we'll reuse them):

customers

orders

INNER JOIN — only matched rows on both sides:

SELECT c.name, o.amount
FROM   customers c
JOIN   orders o ON o.customer_id = c.id;

Note Alice appears twice (2 orders), Carol/Dan are gone, orphan order 103 is gone.

LEFT JOIN — every customer, orders where present, NULL otherwise:

SELECT c.name, o.amount
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.id;

FULL OUTER JOIN — everyone from both sides, NULL on the missing side:

ANTI JOIN — customers who have never ordered:

SELECT c.name
FROM   customers c
WHERE  NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- or:
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Both return Carol and Dan.

Venn-diagram cheat sheet:

Decision table — which join to pick:

Row-multiplication warning. If customers has 4 rows and orders has 4 rows, an INNER JOIN can return up to 4 × 4 = 16 rows (Cartesian) if your join predicate is missing. Forgetting the ON clause is the #1 way to bring down a small database — always double-check.

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

Save as joins_lab.py:

import sqlite3
conn = sqlite3.connect(":memory:"); cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER,     -- deliberately no FK for orphan demo
    amount INTEGER,
    order_date TEXT
);
INSERT INTO customers VALUES (1,'Alice'),(2,'Bob'),(3,'Carol'),(4,'Dan');
INSERT INTO orders VALUES
    (100, 1, 500, '2026-01-10'),
    (101, 1, 300, '2026-02-05'),
    (102, 2, 800, '2026-01-20'),
    (103, 5, 200, '2026-03-01');   -- orphan
""")
 
def run(label, sql):
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print("  ", row)
 
run("INNER JOIN — only matched", """
    SELECT c.name, o.amount FROM customers c
    JOIN orders o ON o.customer_id = c.id
    ORDER BY c.name;""")
 
run("LEFT JOIN — every customer, orders if present", """
    SELECT c.name, o.amount FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    ORDER BY c.name, o.amount;""")
 
# SQLite added FULL OUTER JOIN in 3.39 (2022). If it errors, simulate with UNION.
try:
    run("FULL OUTER JOIN — orphan surfaces", """
        SELECT c.name, o.amount FROM customers c
        FULL OUTER JOIN orders o ON o.customer_id = c.id;""")
except sqlite3.OperationalError:
    run("FULL OUTER (simulated with UNION)", """
        SELECT c.name, o.amount FROM customers c
        LEFT JOIN orders o ON o.customer_id = c.id
        UNION
        SELECT c.name, o.amount FROM customers c
        RIGHT JOIN orders o ON o.customer_id = c.id;""")
 
run("ANTI JOIN — customers who never ordered (NOT EXISTS style)", """
    SELECT c.name FROM customers c
    WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);""")
 
run("ANTI JOIN — same result via LEFT JOIN … IS NULL", """
    SELECT c.name FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    WHERE o.id IS NULL;""")
 
run("Orphan-order check — orders pointing at missing customers", """
    SELECT o.id, o.customer_id FROM orders o
    LEFT JOIN customers c ON c.id = o.customer_id
    WHERE c.id IS NULL;""")
 
# ROW MULTIPLICATION DEMO — count orders per customer
run("WRONG: counting orders after INNER JOIN (Alice: 2 rows, Bob: 1, Carol/Dan: missing)", """
    SELECT c.name, COUNT(*) AS cnt FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name;""")
 
run("RIGHT: order count including zero (LEFT JOIN + COUNT of matched column)", """
    SELECT c.name, COUNT(o.id) AS cnt FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.name ORDER BY c.name;""")
 
# Cartesian mistake — forgetting the ON
run("DANGER: missing ON → Cartesian product (4×4=16 rows)", """
    SELECT c.name, o.id FROM customers c, orders o LIMIT 20;""")
 
conn.close()

Checklist — observe when you run:

1. INNER JOIN shows 3 rows — matches only. Carol, Dan, and the orphan disappear.
2. LEFT JOIN shows 5 rows — Carol and Dan appear with NULL amount. This is what you want for "customers report."
3. FULL OUTER JOIN (or the UNION fallback) surfaces the orphan order (NULL name, amount 200). This is a data quality check — orphans usually indicate a missing FK constraint.
4. Both ANTI JOIN forms return Carol and Dan. Prefer NOT EXISTS for correctness with NULLs; prefer LEFT JOIN … IS NULL for readability.
5. The "WRONG" count uses COUNT(*) after INNER JOIN — Carol/Dan are missing entirely. The "RIGHT" version uses COUNT(o.id) after LEFT JOIN — nulls counted as zero.
6. The Cartesian query returns 16 rows — that's your reminder to always specify ON.

Try this modification: add a products (id, name, price) table and an order_items (order_id, product_id, qty) bridge. Write one query that returns each customer's total spend (SUM(price * qty)), including customers with zero spend. You'll need two joins + a LEFT JOIN + COALESCE(SUM(...), 0).

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Joins are where SQL performance conversations begin. Every query planner spends most of its time deciding join order and join algorithm — nested loop, hash join, merge join. In Postgres you can peek with EXPLAIN ANALYZE; in Spark with df.explain(mode='formatted').

Three join algorithms — the ones you'll actually see in EXPLAIN output:

• Nested-loop join — good for tiny tables or highly-selective predicates on indexed join keys. O(n × m) worst case.
• Hash join — build a hash of the smaller side, probe with the larger side. O(n + m). Postgres/BigQuery default when neither side is sorted.
• Sort-merge join — sort both sides on the join key, then walk in lockstep. Good when data is already sorted (indexes, Spark shuffle output).

War story — the missing index. A B2B SaaS ran SELECT * FROM invoices i JOIN customers c ON c.id = i.customer_id WHERE c.tier = 'enterprise'. Fast in staging (10k rows), timed out in prod (30M rows). Cause: no index on customers.tier. Fix: CREATE INDEX ON customers(tier);. Query dropped from 45s to 80ms. Rule: every column you filter on OR join on should be considered for an index.

War story — the row-multiplication bug. A marketing dashboard joined campaigns to campaign_emails (1:many) to email_events (1:many again) and did SUM(campaign_cost). The cost was multiplied by (emails × events) — off by a factor of ~40x. The dashboard reported ₹4 crore per month spent on ads when actual was ₹10 lakh. Nobody noticed for a quarter because "big number = growth." Fix: aggregate before joining, using a CTE (session S040).

Gotchas top teams handle:

• NULL in join keys. NULL = NULL is NULL, so NULL on either side never matches, even in FULL OUTER. Use COALESCE or dedicated null-safe operators (IS NOT DISTINCT FROM in Postgres).
• SELECT * with join returns all columns from both tables — often 60+ columns, half unused, and column name collisions if both have id. Always name columns and use aliases.
• Case sensitivity of join keys. Joining users.email to crm_contacts.email with mixed case silently drops rows. Normalise on write, or LOWER(...) on both sides (kills the index — sad).
• Cardinality estimation errors. The planner sometimes picks the wrong join order for very skewed data. ANALYZE mytable refreshes statistics and often fixes it.
• Spark shuffle joins on huge datasets — broadcast small tables (broadcast(smallDf) hint) to avoid shuffling 100 GB across the network.

The senior-engineer tell: they say "let's check row counts before and after each join" and they always aggregate in CTEs before joining downstream tables.

(e) Quiz + exercise · 10 min

1. INNER vs LEFT: which one silently drops rows, and when does that matter?
2. Explain "row multiplication" in one sentence with an example.
3. What's the difference between LEFT JOIN … WHERE right.id IS NULL and NOT EXISTS (…)? When would you prefer each?
4. Why does COUNT(*) after a LEFT JOIN often report the wrong number for "customers with no orders"?
5. Name the three main join algorithms a query planner picks between and one-line when each is best.
6. Stretch (connects to S035 Relational Model & S036 SQL Basics): the orders.customer_id column had no FOREIGN KEY constraint, which is how the orphan order (customer_id=5) survived. Rewrite the schema with a real FK and describe what happens if you now try to insert that orphan row.

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is Joins? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S038): Aggregations — GROUP BY, HAVING, Subqueries
• Previous (S036): SQL Basics — SELECT, WHERE, ORDER BY, LIMIT
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.