🔗Joins overview

Joins compose relations sideways

After FROM lists your inputs, each JOIN answers: for each row built so far, which rows from the next table satisfy the ON predicate? INNER keeps only pairs that match; OUTER variants keep driving rows even when the other side has no partner, padding missing columns with NULL. That choice alone decides whether “orphan” instruments, patients without visits, or SKUs without stock still appear in a report.

Under the hood the engine still implements relational composition (typically restricted Cartesian product + filter). If join keys are not unique on either side, rows multiply: this is not a bug, it is multiset semantics, so you must know the cardinality you expect per hop before you trust sums or counts.

🧭Pick the join for the story you are telling

Start from which rows must survive unchanged, then choose INNER vs OUTER.

Join	Unmatched left?	Unmatched right?	Typical story
INNER	No	No	Mandatory relationship: order lines that must have a parent order and a known SKU.
LEFT	Yes	No	Optional enrichment: all patients, join to last visit if any.
RIGHT	No	Yes	Mirror of LEFT, same math; teams often rewrite as LEFT for one reading direction.
FULL	Yes	Yes	Reconciliation: rows only in warehouse, only in source, or in both with differing attributes.

📚Logical order (why WHERE can “break” OUTER joins)

Rough SQL standard pipeline: FROM → WHERE → GROUP BY → HAVING → window → SELECT → DISTINCT → ORDER BY.

WHERE filters rows after joins are formed but before grouping. A predicate like WHERE right.id IS NOT NULL on a LEFT JOIN discards unmatched left rows, turning it into an INNER join in effect.
Predicates that belong to the optional match itself (e.g. “only spring offers”) often belong in the ON clause so unmatched catalog rows still survive with NULL offer columns, then optional global filters apply in WHERE.
For INNER joins, many predicates can live in ON or WHERE; optimizers usually flatten equivalent forms, readability and OUTER correctness matter more than micro-placement there.

ON vs USING

ON a.id = b.id is explicit and portable. USING (id) is shorter when names match exactly, fine in notebooks; production codebases often ban it because schema renames silently change meaning.

Avoid NATURAL JOIN

It pairs columns with the same name, not columns that should relate. A future column rename can join the wrong attributes with no syntax error.

FROM orders o, customers c WHERE …

Comma-separated tables without a join condition produce a full Cartesian product, one forgotten line in a thirty-table report and you ship a cross product.

FROM orders o INNER JOIN customers c ON …

Prefer explicit JOIN syntax so every relationship has a named ON clause in the same visual block reviewers scan.

SQL

INNER join (only matching pairs)

SELECT r.run_id, i.instrument_tag
FROM sequencing_runs r
INNER JOIN instruments i ON i.instrument_id = r.instrument_id
WHERE r.run_date >= DATE '2026-04-01';

✓Illustrative result set

run_id	instrument_tag
91021	SEQ-A1
91022	SEQ-A1

SQL

CROSS JOIN: intentional Cartesian product

SELECT a.well_id, b.dye_code
FROM plate_layout a
CROSS JOIN dye_panel b
LIMIT 6;

✓Illustrative result set

well_id	dye_code
A01	FAM
A01	HEX
A02	FAM
A02	HEX
A03	FAM
A03	HEX

⚡ Advanced / dialect note

Physical join algorithms (nested loop, hash, merge) are optimizer choices, the relational result is the same for a given INNER predicate. OUTER joins restrict how freely the planner can reorder joins; when tuning, compare estimated vs actual row counts at each join node.

Join debugging playbook

When row counts explode or rows vanish, walk this list before rewriting in the app layer.

My LEFT JOIN “became” an INNER join, where did the NULL rows go?

A predicate like WHERE right.id IS NOT NULL or WHERE right.status = 'OPEN' on nullable right columns filters out unmatched left rows after the join. Move optional predicates into the ON clause (or use a subquery) so NULL-padding survives, then apply global filters in WHERE.

Why did my COUNT double after adding a join?

One-to-many relationships multiply rows: each parent repeats for every child. Aggregate in a subquery or CTE at the child grain first, then join the single row per parent, or use COUNT(DISTINCT …) only when the distinct set truly matches the metric definition.

When is CROSS JOIN acceptable?

When you intentionally need every combination (grids, generators) and the cardinality is bounded, pair with strict filters or small dimension tables. Never use comma-joins as shorthand for INNER; reviewers cannot see missing predicates.

Cardinality checklist

Before aggregating, write the expected multiplier next to each join: “patient → visits (1:N), visit → diagnosis codes (N:M through bridge).” If the product does not match row counts in a dry-run COUNT(*), fix the join graph, not DISTINCT: unless dedup is truly part of the metric.

🤯Join Ordering: The N! Problem

The optimizer's hardest job is picking which tables join first.

Combinatorial Explosion

If you join 10 tables, there are 10! (3,628,800) possible ways to order them. The difference between the best and worst order can be 1,000,000x in latency. The optimizer uses Dynamic Programming or Genetic Algorithms to find a "good enough" plan within milliseconds.

Left-Deep Trees

Standard strategy: join two tables, then join the result with the third, and so on. This keeps memory usage low by keeping only one intermediate set in flight.

Bushy Trees

More complex: join (A+B) and (C+D) separately, then join those results. Can be faster for massive parallel joins but harder for the engine to plan.

🧮Relational Algebra Roots

SQL is just a syntax for set operations.

Operator	SQL Keyword	Mechanical Goal
Selection (σ)	WHERE	Reduce rows (Vertical filtering).
Projection (π)	SELECT	Reduce columns (Horizontal filtering). Decreases row width in memory.
Join (⋈)	JOIN	Combine sets. The "Sideways" composition.

Distributed context

In distributed SQL (Spark, CockroachDB), joins come in two flavors: Broadcast Joins (send small table to all nodes) and Shuffle Joins (redistribute both tables by key). Shuffling is expensive, always join by shard keys when possible.

TL;DR

Joins combine relations on predicates, almost always equality on foreign keys in OLTP. The hard part is cardinality: one-to-many joins duplicate left rows; many-to-many needs a bridge table. Filtering belongs in WHERE (inner semantics) vs ON vs post-join predicates for outers, misplaced filters change results. Draw the schema before writing SQL.