SQL Joins

TL;DR: Pick the join type by which non-matching rows you need to keep: inner drops both sides' misses, left keeps all left rows, full keeps all of both. Use EXISTS / NOT EXISTS for semi- and anti-joins when you only want to filter by presence, not pull columns. The two bugs that bite everyone: an unintended fan-out when the join key is not unique, and a left join that you accidentally turn into an inner join by filtering the right table in WHERE.

The four base types

A join matches rows from two tables on a predicate (usually key equality) and decides what to do with the rows that find no match.

The mental model: start from the cross product, keep rows passing the ON predicate, then for outer joins add back the unmatched rows from the preserved side with NULLs filling the other columns.

Semi- and anti-joins: filter, do not fetch

Sometimes you do not want columns from the second table, only to know whether a match exists. That is a semi-join (keep rows with a match) or an anti-join (keep rows with no match). Expressed with EXISTS:

-- semi-join: customers who placed at least one order
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- anti-join: customers with zero orders
SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Why prefer this over an inner join plus DISTINCT? A semi-join returns each left row at most once, so it cannot fan out, and the planner can stop scanning at the first match. The anti-join via NOT EXISTS is also the safe way to express "not in a set": NOT IN with a subquery that can return NULL silently returns zero rows, because x NOT IN (NULL) is UNKNOWN. NOT EXISTS has no such NULL trap.

The fan-out trap

This is the bug that quietly inflates revenue numbers. A join multiplies rows when the join key is not unique on the side you join to. Join orders to order_items (several rows per order) and you get one row per line item, not per order. If you then SUM(orders.amount), every order's amount is counted once per item it has, and your total is too high.

-- WRONG: order amount double-counted per line item
SELECT SUM(o.amount)
FROM orders o JOIN order_items i ON i.order_id = o.id;

The discipline: before joining, know the grain (one row per what?) of each table and confirm the join key is unique on the side you join to. If it is not, aggregate that side to the right grain first, or move it to a semi-join. A related trap: a LEFT JOIN followed by a filter on the right table in WHERE (such as WHERE o.status = 'paid') drops the NULL rows and silently becomes an inner join. Put that condition in the ON clause to keep the left rows.

How the planner executes a join

The join type is logical; the algorithm is the planner's choice from sizes, indexes, and statistics.

Nested-loop wins when one side is tiny or the inner side has an index on the key. Hash join is the workhorse for two large unsorted tables: build a hash table on the smaller input, probe with the larger. Merge join is cheapest when both inputs arrive sorted on the key, since it is a single linear pass. A non-equality condition (a range, <>) kills hash and merge, leaving nested loop, which is why range joins are slow. Read EXPLAIN: a nested loop over two large tables usually means a missing index.

Why interviewers probe this

Joins are where data bugs hide because they fail silently with valid-looking output. The interviewer screens for whether you reason about grain and cardinality before writing the join, not after the numbers look wrong. The strong move is to say the grain of each table out loud and name the type by which rows you must preserve. The held-back follow-up is almost always a fan-out scenario ("also join shipments, which has many rows per order, and keep revenue correct") or "make this NOT IN handle NULLs," to see if you reach for NOT EXISTS.

Common misconceptions

• "LEFT JOIN guarantees one row per left row." Only if the right key is unique. A non-unique right key fans the left row out into duplicates.
• "A filter on the right table belongs in WHERE." For a LEFT JOIN that silently turns it into an inner join. Optional-side conditions go in ON.
• "NOT IN and NOT EXISTS are interchangeable." Not with NULLs. NOT IN over a subquery yielding a NULL returns nothing; NOT EXISTS is safe.
• "The join keyword controls performance." The type is logical; hash vs merge vs nested-loop is the planner's choice, driven by size, sort order, and indexes.

Key takeaways

• Choose the type by which unmatched rows you keep: inner drops, left preserves left, full preserves both.
• Use EXISTS / NOT EXISTS for semi- and anti-joins; they cannot fan out and dodge the NULL trap.
• Know each table's grain before joining; a non-unique key fans out rows and corrupts aggregates.
• Hash join for big unsorted inputs, merge for pre-sorted, nested-loop for tiny or indexed sides.