Introduction

A join evaluates a boolean predicate over the cross product of two tables and keeps only the row pairs for which the predicate is true — so a join is not a fourth kind of query but a controlled, filtered pairing of rows, and every “type” of join is just a rule for what to do with the pairs that don't match.

The mechanism: predicate over a pairing

Conceptually the engine forms every combination of a left row with a right row (the Cartesian product), tests your ON predicate against each combination, and emits the survivors. The four logical join types differ only in how they treat rows on one side that found no partner:

• INNER JOIN — emit only matched pairs; unmatched rows on either side vanish.
• LEFT JOIN — emit all matched pairs, plus every unmatched left row padded with NULLs on the right.
• RIGHT JOIN — the mirror image: keep all right rows, pad missing left columns with NULL.
• FULL OUTER JOIN — keep matched pairs and the unmatched rows from both sides, padding the absent side with NULL.

Two terms the original page mis-filed as “special join types” actually describe the predicate, not a category: an equi-join is any join whose ON uses only equality (a.x = b.y); a natural join is an equi-join where the engine auto-picks the predicate from columns that share a name. A self-join is the one genuinely different shape — it joins a table to itself under two aliases. So the real taxonomy is two orthogonal axes: what we keep (inner / left / right / full) and how we match (equi, theta, natural, cross).

Worked example: trace one join, row by row

Two tables. employees has a dept_id that may be NULL (a new hire not yet assigned), and there is a department, 40 Legal, with no employees yet.

SELECT e.name, d.dname
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

Trace the predicate e.dept_id = d.dept_id against each candidate pairing:

1. Ann(10) × Sales(10) → 10 = 10 → true → emit (Ann, Sales).
2. Bo(20) × Eng(20) → true → emit (Bo, Eng).
3. Cy(10) × Sales(10) → true → emit (Cy, Sales).
4. Di(NULL) × every dept → NULL = 10 is UNKNOWN, never true → Di is dropped.
5. Legal(40) finds no employee → dropped.

So INNER yields 3 rows. Swap in each outer variant and only the unmatched survivors change:

Notice Di and Legal are exactly the rows that failed the predicate — the outer variants are nothing more than a policy on those failures.

The systems payload: how the engine actually does it

The Cartesian-product picture is the semantics, not the implementation — no real engine materializes every pair. The optimizer picks one of three physical algorithms, and knowing which one is running is half of join performance debugging:

Hash and sort-merge only work for equi-joins — you cannot hash or merge on < or BETWEEN. A range or inequality (theta) predicate forces nested-loop, which is why an innocent ON a.ts BETWEEN b.start AND b.end can silently become O(R × S). Run EXPLAIN (or EXPLAIN ANALYZE) and read which of these three node types the planner chose.

Pitfalls

• The accidental Cartesian product. Omit the ON clause (or comma-join in the old FROM a, b style with no WHERE) and you get every pair — a 100k × 100k join is 10 billion rows. The query doesn't error; it just hangs.
• WHERE on an outer join silently turns it inner. LEFT JOIN departments d ON e.dept_id=d.dept_id WHERE d.dname='Sales' drops Di, because the NULL-padded row fails d.dname='Sales'. Filters meant for the optional side belong in the ON clause, not WHERE.
• NULL never equals NULL. Di's NULL dept matched nothing because NULL = anything is UNKNOWN. Joining on a nullable column quietly discards those rows under an inner join.
• NATURAL JOIN is a footgun. It matches on all like-named columns. Add a generic created_at or id column to one table later and the join condition silently changes, returning fewer rows — with no syntax change to alert you. Prefer an explicit ON or USING (dept_id).
• Fan-out double counting. Joining before aggregating, when the right side has multiple matches per left row, multiplies rows — a SUM over a one-to-many join can double real totals. Aggregate first, then join.

Takeaways

• A join is a predicate filter over a row pairing; the four logical types (INNER/LEFT/RIGHT/FULL) differ only in how they handle rows that found no partner.
• “Equi”, “theta”, and “natural” describe the match condition; only “self” describes a different join shape. Don't conflate the two axes.
• The optimizer implements joins as nested-loop, hash, or sort-merge — only nested-loop handles non-equality predicates, so a range condition can quietly cost O(R × S). Read EXPLAIN.
• The dangerous bugs are silent: missing ON, WHERE on an outer side, NULL keys, and NATURAL JOIN — none of them raise an error.

Sources: Garcia-Molina, Ullman & Widom, Database Systems: The Complete Book, ch. on join algorithms (nested-loop, hash, sort-merge); Hellerstein & Stonebraker, Readings in Database Systems (query execution); the PostgreSQL documentation on join types and the planner's Nested Loop / Hash Join / Merge Join nodes; the SQL:1992/2016 standard for NATURAL and OUTER JOIN semantics. Re-authored and deepened for this guide — replaced the analogy-only prose with the cross-product-and-predicate mechanism, a row-by-row worked trace, the join-algorithm payload, and corrected the framing that listed equi/natural joins as a separate join category rather than as join conditions.

🎯 STRICT STANDOUT: Why / mental model / when-not / worked / failure / hostile panel — Introduction (Joins)

Why this concept exists (judgment layer)

Join types are policies on unmatched pairs; equi/natural/theta are match conditions — orthogonal axes. Physical nested-loop / hash / merge decide whether your join finishes.

Mental model (install this intuition)

Semantics: filter Cartesian product by ON. INNER drops non-matches; LEFT/RIGHT/FULL pad with NULL. Implementation never builds full product — NL/hash/merge. NULL join keys never equal under =.

Worked example with numbers or traced steps

employees: Ann10, Bo20, Cy10, Di NULL | depts: 10 Sales, 20 Eng, 40 Legal
INNER: (Ann,Sales)(Bo,Eng)(Cy,Sales) — Di and Legal gone
LEFT adds (Di,NULL); RIGHT adds (NULL,Legal); FULL both
WHERE d.dname='Sales' after LEFT → drops Di (outer becomes inner)
Theta ON a.ts BETWEEN b.start AND b.end → often nested-loop O(R×S)

When NOT to use / named alternative

Avoid NATURAL JOIN (silent condition change when columns added). Avoid bare comma joins without WHERE. Aggregate after one-to-many join only if you intend fan-out; else aggregate then join.

Failure mode & ops fingerprint

Fingerprint: query hangs on missing ON (100k×100k); report doubles revenue after join-before-sum; NULL dept employees vanish under INNER; EXPLAIN Nested Loop on range join.

Hostile-panel drills (defend the decision)

Q1. Equi-join vs join type?
Model answer: Equi describes the predicate (equality); INNER/LEFT describe which unmatched rows survive. Orthogonal.

Q2. When is hash join unavailable?
Model answer: Non-equality (theta) predicates — hash and merge need equi-join keys; planner falls back to nested-loop.

Q3. Why does WHERE on right columns kill LEFT JOIN semantics?
Model answer: NULL-padded unmatched left rows fail the WHERE predicate and disappear — same as INNER.