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

Runtime contract: The complete lab runs in memory on SQLite 3.39+ (RIGHT and FULL OUTER JOIN were added in 3.39.0). Its fallback full-join query is also checked against the native result. PostgreSQL 16 supplies the documented physical-plan reference; SQLite execution does not reproduce a PostgreSQL hash/merge join.

Why this session exists

Normalisation separates facts with different identities and lifetimes. A join recombines related rows, but the relationship need not be one-to-one. Correct SQL must answer two independent questions: which matches or nonmatches belong in the report, and what does one output row represent?

You will choose INNER, LEFT, RIGHT, FULL, semi/anti and self joins; predict multiplicity; distinguish equality and null-safe predicates; write two safe anti-join forms; and reason about nested-loop, hash, merge and distributed broadcast costs without assuming a production speed hierarchy.

Prerequisites

Read the relational model and SQL basics. Know candidate keys, bag semantics, three-valued logic and why ORDER BY is required for presentation order.

Optional archived resources

These original links are retained for continuity, not used as technical evidence. Video titles, attribution, duration and playback are not reverified; a search link is not a particular lecture. Use the versioned primary sources below for contracts.

• SQL Joins Explained — Venn Diagram Approach
• Joins in SQL
• SQL JOIN — Practice Queries against a Realistic Dataset
• Joined Tables — PostgreSQL Reference
• The Join Operation — Use the Index, Luke
• SQL JOIN Tutorial — Interactive

Join intuition: all matching pairs, not VLOOKUP

A spreadsheet lookup often picks one match. A SQL inner join emits every pair for which its predicate is TRUE. If a key appears m times on the left and n times on the right, an equality join emits m×n pairs for that key. NULL keys do not match via ordinary =, because the predicate is UNKNOWN.

• INNER: matching pairs only.
• LEFT: matching pairs, plus one NULL-extended right side for each left row with no match.
• RIGHT: the mirror of LEFT; swapping inputs can often make the query easier to read.
• FULL: matching pairs plus unmatched rows from either input, with the absent side NULL-extended.
• Semi join: keep a left row if a match exists, without multiplying it by match count; express with EXISTS.
• Anti join: keep a left row only when no match exists; express with NOT EXISTS or a carefully chosen LEFT/IS NULL pattern.
• CROSS: every pair, intentionally. Four rows times four rows gives sixteen.
• Self join: use two aliases for two roles of the same table, such as employee and manager. It is not a new unmatched-row rule.

The relational-algebra/SQL lineage explains why joins are central, but neither a Venn diagram nor a historical “default algorithm” chart predicts multiplicity or a modern engine's plan. PostgreSQL documents the three physical methods; SQLite implements its joins as nested scans. Syntax and implementation are different layers.

The tables and exact result shapes

Order 103 deliberately lacks a parent. The lab omits that FK to demonstrate detection, not to recommend omitting integrity checks in production.

RIGHT JOIN preserving orders returns four rows: Alice twice, Bob once, and a NULL customer for order 103. FULL does not mean a deduplicated union of input rows; Alice still appears twice because her two orders are distinct matches.

For these inputs customer IDs are unique, so matched-order count cannot exceed the order count. That bound is not true for arbitrary inputs. LEFT output is at least the number of left rows before later filters; exceeding it can be a perfectly correct one-to-many result.

NULL, ON, USING and anti joins

An equality join does not match two NULL keys, even under FULL OUTER. PostgreSQL's IS NOT DISTINCT FROM, or SQLite's IS, deliberately treats two NULLs as equal. That may create a large missing-key peer group; only use it if the domain calls missing values equivalent.

To retain every customer but match only orders over 400:

SELECT c.id, c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 400
ORDER BY c.id, o.id;

Answer: Alice–100, Bob–102, Carol–NULL, Dan–NULL. Moving o.amount > 400 to WHERE removes Carol and Dan. A filter on the preserved side, or a predicate accepting NULL, has different semantics; not every WHERE clause turns an outer join into an inner one.

These two anti-join forms return Carol and Dan:

SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
ORDER BY c.id;
 
SELECT c.id, c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.id;

The tested right-side marker o.id must be non-null on a real match. Left join keys need not both be NOT NULL for equivalence. Testing a nullable payload such as o.note IS NULL can misclassify a real matched row as an anti match. NOT EXISTS is especially direct when no right-hand payload is needed. Do not assert identical plans; the optimiser can recognise anti-join patterns differently.

USING(customer_id) is equality on named shared columns and projects one merged join column rather than both. NATURAL JOIN derives its matching-column list from all common names; adding a same-named column can change the answer, so explicit ON/USING is safer for stable application contracts.

A bag-preserving FULL JOIN fallback

The lab's fallback uses UNION ALL, not UNION, and emits each unmatched right row only after testing a left marker that cannot be NULL on a match. That proof is separate from the choice of equality predicate. For ordinary =, a nullable join key cannot match while NULL, so it can serve as that marker. With null-safe equality it cannot: a real matched NULL key would be mistaken for an unmatched row. Use a non-null identity instead. Compare row multiplicities, not just sets, and include duplicate-valued rows and NULL keys in tests.

PostgreSQL 16 supports the IS NOT DISTINCT FROM predicate, but that is not a claim that every join type can plan every predicate. In particular, do not assume a FULL JOIN on that expression is accepted as a hash/merge-joinable equality clause. The executed lab uses SQLite equality joins; PostgreSQL null-safe outer-join forms and plans are documentation-only here.

Row multiplication and the correct measure grain

Three orders with four items each produce twelve line rows, but not every SUM is multiplied by twelve. An order-header amount is repeated four times per order; an item amount appears once per line and may be exactly the measure you intended to sum. An AVG of header values becomes weighted by item count; if counts happen to match, it can coincidentally remain unchanged.

In the lab order 100 has two lines whose gross total is 500, and order 101 has one line worth 300. Joining items then summing orders.amount gives Alice 500+500+300=1300, not her actual 800. The two-line example is a grain error, not a numeric precision issue.

If you need only order revenue, do not join items at all. If you need both order revenue and item counts, aggregate the child first:

WITH item_counts AS (
    SELECT order_id, COUNT(*) AS line_count
    FROM order_items GROUP BY order_id
)
SELECT c.id, c.name, COALESCE(SUM(o.amount),0) AS total,
       COALESCE(SUM(i.line_count),0) AS lines
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN item_counts i ON i.order_id = o.id
GROUP BY c.id, c.name
ORDER BY c.id;

Answer: Alice (800,3), Bob (800,1), Carol (0,0), Dan (0,0). COUNT(*) after LEFT JOIN would count Carol's placeholder row as one; COUNT(o.id) yields zero. Group on identity, not just name: two customers can share a name.

A correlated scalar aggregate is another way to keep one value per customer: SELECT c.id, (SELECT SUM(o.amount) FROM orders o WHERE o.customer_id=c.id) AS total FROM customers c. It gives NULL for nonbuyers unless a reporting zero policy wraps the aggregate. A PostgreSQL LATERAL relation can also reference the outer row, but it is not automatically scalar: multiple returned rows still multiply the outer row. An indexed lookup may suit a small outer population; preaggregation may suit a broad report. Inspect actual plans rather than assuming correlation means a repeated full scan.

Do not “fix” fan-out with SUM(DISTINCT amount): two legitimate orders worth 100 should contribute 200, not 100. For one 100-valued order with two items and three payments, the joined header sum is 600. The repair is identity/grain preservation, not value deduplication.

Payments and shipments: preserve the original failure mechanism

This standalone query recreates the original two-many-sides example without touching the shop tables. The wrong join repeats each payment for every shipment: actual paid 100 becomes 200. Checking only for multiple payments does not “catch it every time”: one payment with two shipments is already enough to duplicate that payment. Count and validate both sides at the intended key.

WITH orders(order_id) AS (VALUES (1)),
     payments(order_id,amount) AS (VALUES (1,40),(1,60)),
     shipments(order_id,shipment_id) AS (VALUES (1,10),(1,11))
SELECT o.order_id, SUM(p.amount) AS wrong_paid
FROM orders o
JOIN payments p ON p.order_id=o.order_id
JOIN shipments s ON s.order_id=o.order_id
GROUP BY o.order_id;
-- (1, 200), but payment-grain total is 100.

WITH orders(order_id) AS (VALUES (1)),
     payments(order_id,amount) AS (VALUES (1,40),(1,60)),
     shipments(order_id,shipment_id) AS (VALUES (1,10),(1,11)),
     paid AS (SELECT order_id,SUM(amount) AS total FROM payments GROUP BY order_id),
     shipped AS (SELECT order_id,COUNT(*) AS parcels FROM shipments GROUP BY order_id)
SELECT o.order_id,COALESCE(p.total,0),COALESCE(s.parcels,0)
FROM orders o LEFT JOIN paid p ON p.order_id=o.order_id
LEFT JOIN shipped s ON s.order_id=o.order_id;
-- (1, 100, 2): both derived inputs are unique on order_id.

Preaggregation is safe only at the actual join grain. Grouping by (order_id, code) and then joining on order_id alone can still fan out. Do not aggregate away dimensions needed for a later filter or attribute a payment to a shipment without a domain relationship. Whether a CTE is inlined/materialised is an engine planning issue, not what makes the grain proof hold.

Discounts exercise: two independent many sides

Add two discounts on order 100 and one on 101:

CREATE TABLE discounts(order_id INTEGER REFERENCES orders(id), code TEXT, amount INTEGER);
INSERT INTO discounts VALUES (100,'WELCOME',50),(100,'BULK',30),(101,'WELCOME',20);

Joining both raw items and raw discounts gives four rows for order 100 and one for 101. Alice's header sum becomes 2300. Bob disappears under an inner discount join, despite having a real order. Solve both problems:

WITH item_counts AS (
    SELECT order_id, COUNT(*) AS lines FROM order_items GROUP BY order_id
), discount_totals AS (
    SELECT order_id, SUM(amount) AS discount FROM discounts GROUP BY order_id
)
SELECT c.id, c.name, COALESCE(SUM(o.amount),0) AS gross,
       COALESCE(SUM(i.lines),0) AS lines,
       COALESCE(SUM(d.discount),0) AS discounts
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN item_counts i ON i.order_id = o.id
LEFT JOIN discount_totals d ON d.order_id = o.id
GROUP BY c.id, c.name ORDER BY c.id;

Answer: Alice gross 800, three lines, discounts 100; Bob gross 800, one line, discounts 0; Carol and Dan all zero. Here orders.amount is explicitly gross before discounts. If it were already net, subtracting the discount again would be a second independent error.

Physical algorithms and output cost

Let left/right input sizes be n and m, and output size k. Any implementation must account for emitting k rows, which can itself be n×m.

A hash table need not fit entirely in memory for a hash join to be possible; a database can batch/spill. Nor is a spill guaranteed to cause an abrupt performance cliff. Sorted access, statistics, join predicates and memory budgets determine a plan. SQLite's local examples do not execute PostgreSQL's hash/merge machinery or a Spark broadcast.

EXPLAIN in PostgreSQL names Nested Loop, Hash Join and Merge Join nodes. Read row estimates versus actual rows, loops, sort/spill and buffer information, rather than treating any Seq Scan as bad. A broad scan can be the right input to a hash join. EXPLAIN ANALYZE executes the query; use a disposable/approved context for expensive work.

Why plans change without a universal winner

For a selective outer input of 10 customers and an indexed inner relation of a million orders, a nested loop can probe only the relevant keys rather than build a hash table for the full inner input. If almost all customers qualify, repeated probes and row fetches may cost more than scanning and hashing once. These are qualitative cost models, not measured crossover thresholds. Duplicate keys add output work to either method. A merge join must emit every combination within equal-key runs, not zip one row from each side.

In PostgreSQL 16, the planner considers paths and useful ordering along with join order; it is not simply greedy over one independently cheapest operator. The documented optimiser uses near-exhaustive search for small join counts and a genetic heuristic above geqo_threshold. Statistics and correlated predicates affect estimates, and ANALYZE refreshes statistics rather than certifying perfect estimates. EXPLAIN actual rows/loops can expose a misestimate; EXPLAIN ANALYZE also executes the query. No PostgreSQL plan was executed for this review.

Historical context is the separation between relational query meaning and physical access paths: the System R lineage explored optimisation without changing the logical result. Modern nested-loop, hash and merge alternatives retain that separation. The old date/default chart is not evidence that one vendor universally chooses a method or invented it in a particular year. IBM's relational history and the PostgreSQL 16 optimiser description support this narrower account.

For distributed joins, a 100 GB dimension is not “small” merely relative to a huge fact table. Broadcast multiplies transferred bytes across recipients and consumes per-worker build memory; it may be infeasible even when shuffling is expensive. Alternatives include repartitioning/co-location, pruning the needed dimension rows, or maintaining a materialised view with explicit freshness and historical-attribute rules. No Spark hint, executor memory threshold or named-company incident is reproduced here.

The Cartesian trap and safe checklist

FROM customers CROSS JOIN orders or a comma-separated FROM list without a matching WHERE condition intentionally yields sixteen pairs here. PostgreSQL plain JOIN orders without ON/USING is a syntax error; not every engine silently converts a missing ON clause to CROSS JOIN. SQLite is more permissive. Use explicit syntax to expose intent; a CROSS JOIN is appropriate for a deliberately bounded product/calendar grid.

Before accepting a joined report:

1. State the grain and key of each input and output.
2. Check uniqueness on the actual join columns, including every component of a composite key.
3. Test zero, one and multiple matches, missing keys and duplicate display names.
4. Compare a known entity and independent totals at the measure's grain.
5. Inspect the plan on representative data; investigate missing child indexes, selectivity, row estimates, sorting and spill rather than prescribing an index from a timing anecdote.
6. If data crosses partitions/shards, assess co-location, replication and data movement. A materialised/denormalised result trades refresh correctness for saved query work, not guaranteed speed.

The original marketing multiplication and large-fact/dimension incidents are retained as these mechanisms, not unsupported claims about named firms or trillions of events. A hypothetical campaign cost repeated across 20 emails with two events each contributes 40 copies when wrongly summed; line-level event counts need not be wrong.

Hands-on: every join shape

Save as joins_lab.py. SQLite FK checking is enabled for the declared item relationship; the intentionally orphanable orders/customer relationship remains unconstrained. FULL OUTER and its UNION ALL emulation project the same columns. They are compared as bags, because plain UNION could incorrectly remove distinct duplicate-valued rows.

"""joins_lab.py — every join flavour, plus row-multiplication demo."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys=ON")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (
    id           INTEGER PRIMARY KEY,
    customer_id  INTEGER,                       -- no FK on purpose (orphan demo)
    amount       INTEGER NOT NULL,
    order_date   TEXT    NOT NULL
);
CREATE TABLE order_items (
    order_id     INTEGER NOT NULL REFERENCES orders(id),
    product      TEXT NOT NULL,
    qty          INTEGER NOT NULL,
    unit_price   INTEGER NOT NULL
);
 
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 customer_id=5
INSERT INTO order_items VALUES
    (100, 'Shirt', 1, 200),
    (100, 'Cap',   1, 300),          -- 2 items on order 100
    (101, 'Mug',   3, 100),          -- 1 item on order 101
    (102, 'Book',  4, 200);          -- 1 item on order 102
""")
 
def run(label: str, sql: str) -> None:
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print(" ", row)
 
# ---- 1. INNER JOIN — matched only ----
run("INNER JOIN — 3 rows, no Carol/Dan/orphan", """
    SELECT c.name, o.amount FROM customers c
    JOIN orders o ON o.customer_id = c.id
    ORDER BY c.name, o.amount;
""")
 
# ---- 2. LEFT JOIN — every customer ----
run("LEFT JOIN — 5 rows, Carol/Dan appear with NULL amount", """
    SELECT c.name, o.amount FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    ORDER BY c.name, o.amount;
""")
 
# ---- 3. FULL OUTER — SQLite 3.39+ ----
if sqlite3.sqlite_version_info >= (3,39,0):
    run("FULL OUTER JOIN — orphan order 103 surfaces", """
        SELECT c.name, o.id, o.amount FROM customers c
        FULL OUTER JOIN orders o ON o.customer_id = c.id
        ORDER BY c.name NULLS LAST, o.id;
    """)
else:
    run("FULL OUTER (older SQLite: simulate via UNION)", """
        SELECT c.name, o.id, o.amount FROM customers c
        LEFT JOIN orders o ON o.customer_id = c.id
        UNION ALL
        SELECT c.name, o.id, o.amount FROM orders o
        LEFT JOIN customers c ON c.id = o.customer_id
        WHERE c.id IS NULL;
    """)
 
# ---- 4. ANTI JOIN — two syntaxes ----
run("ANTI JOIN — customers who never ordered (NOT EXISTS)", """
    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 (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;
""")
 
# ---- 5. Orphan check — the OTHER direction ----
run("Data-quality: orders with a missing customer", """
    SELECT o.id, o.customer_id FROM orders o
    LEFT JOIN customers c ON c.id = o.customer_id
    WHERE c.id IS NULL;
""")
 
# ---- 6. Row multiplication demo ----
run("WRONG: order count after INNER JOIN with COUNT(*) — Carol/Dan missing", """
    SELECT c.name, COUNT(*) AS cnt FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name;
""")
 
run("RIGHT: LEFT JOIN + COUNT(o.id) — zeros for Carol/Dan", """
    SELECT c.name, COUNT(o.id) AS cnt FROM customers c
    LEFT JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name ORDER BY c.name;
""")
 
# ---- 7. The MULTIPLICATION BUG ----
run("BUG: SUM(amount) after 3-way join → order amounts get multiplied by item count", """
    SELECT c.name,
           SUM(o.amount) AS wrong_total,
           COUNT(*)      AS join_rows
    FROM customers c
    JOIN orders o       ON o.customer_id = c.id
    JOIN order_items oi ON oi.order_id   = o.id
    GROUP BY c.id, c.name;
""")
# Alice: (500+500+300)=1300  Bob: 800  — but 500 got counted TWICE because
# order 100 has 2 items. Multiplied by item count.
 
run("FIX: omit unnecessary item join; aggregate orders at customer grain", """
    SELECT c.name, SUM(o.amount) AS total
    FROM customers c
    JOIN orders o ON o.customer_id = c.id
    GROUP BY c.id, c.name ORDER BY c.name;
""")
 
# ---- 8. Cartesian mistake ----
run("Intentional CROSS JOIN: 4x4 = 16 rows", """
    SELECT c.name, o.id FROM customers c CROSS JOIN orders o ORDER BY c.id, o.id LIMIT 20;
""")
 
# ---- 9. EXPLAIN — see the plan ----
run("EXPLAIN plan for INNER JOIN with WHERE", """
    EXPLAIN QUERY PLAN
    SELECT c.name, o.amount FROM customers c
    JOIN orders o ON o.customer_id = c.id
    WHERE c.name = 'Alice';
""")
 
conn.close()

The lab's key outputs are INNER 3, LEFT 5, FULL 6, anti 2, reverse anti order 103, and Cartesian 16. Alice's faulty total is 1300; removing the unnecessary item join returns 800. That latter query is labelled honestly as removing the fan-out, while the CTE examples above implement actual child preaggregation.

Recall and stretch answers

1. When LEFT over INNER? When unmatched left entities belong in the result, such as nonbuying customers. Optional-side predicates belong in ON when they define matching eligibility.
2. What multiplies? Matching combinations. A measure is wrong only when evaluated at a grain inconsistent with those combinations.
3. Anti forms equivalent? With the same predicate and a non-null-on-match right marker, yes; nullable left keys do not by themselves invalidate equivalence.
4. COUNT on an unmatched customer? COUNT(*)=1, COUNT(o.id)=0 before grouping the placeholder away.
5. Algorithms? Indexed nested loops for suitable selective probes, hashing for suitable equijoins, merging for ordered inputs; include output/spill costs.
6. Ten times too many events? Check whether users is unique on the join key, missing tenant/key predicates and duplicate upstream rows. Repeated event user IDs alone are expected and do not multiply event rows against a unique user table.
7. NULL equality? Ordinary = yields UNKNOWN; null-safe predicates change it intentionally.
8. Comma syntax? Legal with a WHERE join condition, but easy to damage accidentally. Explicit JOIN makes the relationship visible.
9. Self join? employees e LEFT JOIN employees m ON m.id=e.manager_id preserves top-level employees and retrieves their optional manager. Cyclic hierarchy traversal is a separate recursive problem.
10. Spend under 1000 including nonbuyers? Use the query below. On this fixture Alice and Bob each show 800; Carol and Dan show 0.

SELECT c.id, c.name, COALESCE(SUM(o.amount),0) AS total_spend
FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
GROUP BY c.id, c.name
HAVING COALESCE(SUM(o.amount),0) < 1000
ORDER BY total_spend DESC, c.id;

Explain aloud: a join emits predicate-satisfying pairs plus specified nonmatches; use LEFT when absence matters; protect the measure grain by uniqueness, preaggregation or existence tests rather than DISTINCT patches.

Explain-out-loud answer guide

1. What is a JOIN? It combines every pair of input rows satisfying its predicate, then includes the nonmatches required by its outer-join rule; multiplicity is part of the result.
2. Why LEFT instead of INNER? When missing right-side matches must leave the left entity visible; demonstrate Carol's NULL-extended row rather than relying on a Venn diagram.
3. What is row multiplication and how do you prevent incorrect totals? A header measure repeats once per joined child combination; Alice's 500 order is counted twice, making 1300 instead of 800. Preaggregate each independent child to the actual join key, or avoid an unnecessary child join.
4. How do you find nonbuyers? NOT EXISTS tests absence directly; LEFT/IS NULL needs a marker guaranteed non-null on a real match. Right-side NULL keys do not globally poison NOT EXISTS as they can NOT IN.

Sources and next steps

Next: aggregations, CTEs, windows and indexes. Fact/dimension joins are not universally LEFT: decide whether unmatched facts must survive and how unknown dimensions are represented.

Primary sources rechecked 10 October 2026: PostgreSQL 16 joined tables, subqueries/EXISTS, EXPLAIN join plans, SQLite SELECT, SQLite query plans, and SQLite 3.39.0 release. The source/evidence ledger distinguishes documentation entailment, local result oracles and unexecuted vendor/distributed plans.