How do you join tables on multiple keys, and why is the key order in a composite index important?

How to think about it

Multi-key joins show up in partitioned tables, slowly changing dimensions, and any schema where no single column uniquely identifies a row. The interviewer is checking two things: whether you know when a composite key is required, and whether you understand how index column order drives performance.

The governing principle: every condition in the ON clause must match simultaneously. There’s no partial credit — one mismatched column means no join for that row pair.

A worked example — match on three columns

Joining sales to budgets on region + product + year, and computing the variance. Only rows matching all three survive:

SELECT s.region, s.product_id, s.year,
       s.revenue, b.budget,
       s.revenue - b.budget AS variance
FROM sales s
JOIN budgets b
  ON s.region     = b.region
 AND s.product_id = b.product_id
 AND s.year       = b.year
ORDER BY s.region, s.product_id;

region	product_id	year	revenue	budget	variance
East	1	2024	5000	4500	500
East	2	2024	3000	3500	-500
West	1	2024	4000	3800	200

The (West, 2, 2023) sale is silently dropped — there’s no budget row for that exact triple, and one mismatched column (the year) is enough to exclude it. Swap to LEFT JOIN and it reappears with a NULL budget. The AND chain is unforgiving by design.

Mixing equality and range conditions

You can combine equality and inequality predicates, but on most engines only the equality columns drive an index seek; the range condition then narrows the scan from that point:

SELECT a.session_id, b.event_id
FROM sessions a
JOIN events b
  ON a.customer_id = b.customer_id    -- equality: drives the seek
 AND a.status      = b.status
 AND b.event_ts BETWEEN a.start_ts AND a.end_ts;   -- range: narrows the scan

Composite index column order

An index on (region, product_id, year) can serve a lookup on region, on region + product_id, or on all three — but not on product_id alone. The leading column must appear in the predicate, or the index is useless:

CREATE INDEX idx_budgets_composite ON budgets (region, product_id, year);

NULL behaviour on multi-key joins

NULL never equals NULL in SQL. If both sides carry NULL in a join column, those rows won’t match — even though the NULLs “mean the same unknown.” For surrogate keys this rarely bites; for natural keys from upstream data, filter the NULLs out explicitly, or use IS NOT DISTINCT FROM (PostgreSQL/BigQuery) when you genuinely want NULL-equal semantics.