SQL Joins

SQL joins combine rows from two relations by testing a join condition. An equality join pairs every left row with every right row whose keys compare TRUE; the join type then decides what happens to rows with no partner. Duplicate keys can multiply the output, while missing partners may disappear or produce NULL columns.

If a query suddenly duplicates rows, the join is usually doing exactly what you asked. Think of two stacks of claim tickets: every left ticket numbered 10 pairs with every right ticket numbered 10. Three tickets on each side make the clerk print nine pair receipts. SQL uses the same uncompromising arithmetic.

How SQL Joins Work

Suppose employees.dept_id should match offices.dept_id:

SELECT e.employee, e.dept_id, o.dept_id, o.city
FROM employees AS e
LEFT JOIN offices AS o
  ON e.dept_id = o.dept_id;

For each non-NULL key k, call the left row group L_k and the right row group R_k. The join contributes |L_k| × |R_k| matched rows. SQL relations have bag semantics here: two rows with the same key remain two rows, so neither side is silently deduplicated.

The join type controls survival after those pairs are formed:

• INNER JOIN keeps only matched pairs.
• LEFT JOIN also keeps every unmatched left row and fills the right columns with NULL.
• FULL OUTER JOIN additionally keeps unmatched right rows and fills the left columns with NULL.

This is the logical result rule. A database may use a nested-loop, hash, merge, index lookup, or another physical plan to produce the same rows. The SQL query does not promise which one.

SQL Joins Step by Step

The default example has four employees: Ada in department 10, Bo in 20, Cy in 30, and Dee in 40. The offices relation contains NYC and London under key 10, Paris under 30, and Tokyo under 50. The query is a LEFT JOIN.

Start with key 10. Ada has two compatible office rows, so 1 × 2 = 2: the result gets (Ada, 10, 10, NYC) and (Ada, 10, 10, London). In claim-ticket terms, each accepted pair prints its own receipt. Ada is not forced to pick one office; both pairs are part of the relation.

Bo's key 20 has no office partner. A left join preserves Bo and writes (Bo, 20, NULL, NULL). Key 30 gives Cy one partner, so (Cy, 30, 30, Paris) follows. Dee's key 40 misses and becomes (Dee, 40, NULL, NULL).

Tokyo is a right-only row under key 50. LEFT JOIN discards it because only unmatched left rows receive protection. The final result has five rows: three matched pairs plus two left gaps.

The animation presents those rows in a teaching order based on the source tables. SQL does not guarantee result order without an ORDER BY clause, even if one run happens to return this sequence.

Why SQL Joins Multiply Rows

A join matches rows, not distinct key values. That distinction causes the classic "why did my query duplicate everything?" bug.

In the default data, key 10 contributes two rows because one employee matches two offices. Across the whole input, INNER JOIN returns three rows. LEFT JOIN adds Bo and Dee for five. FULL OUTER JOIN also adds Tokyo for six.

The many-to-many preset makes the multiplication impossible to miss: three employees share key 10 and three offices share key 10. Every employee pairs with every office, so the one key group contributes 3 × 3 = 9 result rows. A join that was expected to preserve row count can therefore grow far beyond either input.

How to Debug a Join That Multiplies Rows

Do not begin with DISTINCT. It can hide the symptom while leaving the wrong relationship in place. Start by counting rows per join key on both sides. Any key with counts greater than one on both sides contributes their product, so a left count of 12 and a right count of 8 means 96 matched rows before any other key is considered.

Then decide what the query actually needs:

• Use a join when you need every qualifying left/right combination.
• Use EXISTS when you only need to know whether a partner exists.
• If each left row should receive one right row, define which one with a uniqueness constraint, a stricter predicate, ranking, or aggregation. SQL cannot infer your preferred row.
• Use LEFT JOIN when unmatched left rows must remain visible; use INNER JOIN when they should disappear; use FULL OUTER JOIN only when unmatched rows from both sides belong in the result.

This checklist turns an "exploding join" from a surprise into arithmetic: inspect key cardinality, confirm the required combinations, then choose the survival rule.

Edge Cases

• No overlapping keys: INNER JOIN returns zero rows. LEFT JOIN preserves the left side; FULL OUTER JOIN preserves both sides with NULL padding.
• NULL join keys: under an ordinary equality condition, NULL = NULL evaluates to unknown, not TRUE. Two NULL keys do not form a matched pair.
• Duplicate keys: duplicates participate independently. Two left rows and four right rows for one key contribute eight rows.
• Several unmatched rows under one key: an outer join preserves each row separately. It does not compress the group into one placeholder.
• Dialect support: join syntax is not identical in every database. Check the target database before relying on FULL OUTER JOIN or rewriting it.

Common Mistakes

• Assuming one result row per key. The real unit is a compatible left/right row pair.
• Adding DISTINCT before finding why the key groups multiply. Deduplication can erase evidence of a missing constraint or an incomplete ON condition.
• Writing a LEFT JOIN, then adding WHERE o.city = 'Paris'. That predicate rejects the NULL-padded rows and can make the result behave like an inner join. Put a condition in ON when it should control matching rather than filter the completed result.
• Expecting NULL to match NULL with =. Use the database's null-aware comparison when that is the intended rule.
• Treating displayed order as part of join semantics. Only ORDER BY owns ordering.
• Reading the logical pair matrix as a performance trace. The optimizer is free to produce the same result without materializing that matrix.

A Note on Simplification

This lesson models equality joins, bag semantics, unmatched-row preservation, and NULL padding. It leaves projection rewrites, predicate pushdown, indexes, cardinality estimates, optimizer choices, transaction visibility, collation rules, and physical join algorithms off the canvas. Those topics change how a database finds the rows, not which rows the three demonstrated join types logically return.