Joins
Combining rows from two relations by a predicate: inner, left, right, full, cross, and the nested-loop vs hash implementations.
Level: intermediate
What is it?
A join combines rows from two inputs according to a condition. An inner join emits matching pairs; an outer join also preserves selected unmatched rows with null-extended columns; a cross join forms every pair. The DBMS can use nested-loop, hash, or merge algorithms depending on data and access paths.
Why does it matter?
Relational data are normalized across related tables, so joins reconstruct the combined view an application needs. Understanding their semantics prevents lost rows, accidental row multiplication, and expensive plans.
Mental model
A join is a matching operation between two row sets. First specify what counts as a match; then specify what should happen to rows without a match.
How it works
- The ON predicate defines matches; WHERE filters the joined result and can change the behavior of an outer join.
- One-to-many joins multiply a parent row by the number of matching children.
- LEFT JOIN keeps every left input row; missing right-side values become NULL.
- Nested-loop joins can be effective for a small outer input with an index on the inner side.
- Hash joins build a lookup structure for one input; merge joins exploit compatible ordering.
Steps
- Customers has rows C1 and C2; Orders has two rows for C1 and none for C2.
- INNER JOIN returns only C1 paired with its two orders.
- LEFT JOIN returns both C1 order rows plus one C2 row whose order columns are NULL.
- If counting customers with no orders, test an appropriate right-side key for IS NULL or use NOT EXISTS.
Real-world usage
- Combining normalized tables in application queries.
- Debugging duplicated totals caused by one-to-many joins.
- Reading execution plans and choosing indexes based on join access patterns.
Common mistakes
- Putting a right-table filter in WHERE when you intended to preserve unmatched rows from a LEFT JOIN.
- Joining without a meaningful predicate and accidentally creating a Cartesian product.
- Counting a parent column after a one-to-many join without accounting for row multiplication.
Interview questions
How does LEFT JOIN differ from INNER JOIN?
LEFT JOIN preserves all rows from the left input and fills unmatched right-side columns with NULL; INNER JOIN emits only matching pairs.
Why can a join multiply rows?
If one left row matches several right rows, the output contains one row for each matching pair.
What factors influence hash join versus nested-loop join choice?
Input sizes, join selectivity, available indexes, memory, existing ordering, and the optimizer's cardinality estimates.