Joins
DBMS · 10 interview questions
A join combines rows from two tables on a condition. Inner join keeps only matching pairs. Left outer keeps every row from the left side, filling nulls where the right has no match, and right outer is its mirror. Full outer keeps unmatched rows from both. Cross join produces the cartesian product.
Underneath, the database picks one of three algorithms. Nested loop scans one table and probes the other per row — good when one side is tiny or the join column is indexed. Hash join builds a hash table from the smaller side then streams the larger — good for large unsorted joins on equality. Merge join walks two sorted inputs together — good when both are already sorted, typically by an index.
Knowing the algorithms is what turns 'the join is slow' into an actionable diagnosis, because each one is slow for a different, fixable reason.
Joins interview questions
- What does an inner join return?
- Only rows where the condition matches on both sides. Rows with no counterpart are dropped from both tables.
- What does a left outer join return?
- Every row from the left table, paired with matching right rows where they exist and NULLs where they don't.
- What does a full outer join return?
- All rows from both tables — matched pairs where they exist, and rows from either side padded with NULLs where they don't.
- What is a cross join?
- The cartesian product: every row of the first table paired with every row of the second. m rows and n rows produce m × n. Usually a mistake when it appears unintentionally through a missing join condition.
- What is a self join and when do you need one?
- A table joined to itself using aliases, for hierarchical or comparative data — matching employees to their managers when both live in the same employees table.
- When is a nested loop join the right choice?
- When one side is small, or the join column on the inner side is indexed so each probe is cheap. It degrades badly when both sides are large and unindexed, since the cost approaches the product of the row counts.
- How does a hash join work, and when is it chosen?
- It builds a hash table from the smaller input, then scans the larger and probes for matches. Chosen for large equality joins with no useful index. It needs memory for the hash table, and spills to disk if it doesn't fit.
- When is a merge join used?
- When both inputs are already sorted on the join key, usually because an index provides the order. It walks both in one pass. If the inputs aren't sorted, the sort cost often makes hash join cheaper.
- For an outer join, does it matter whether a condition goes in ON or WHERE?
- Yes, and it's a classic bug. A condition in ON filters before padding, preserving unmatched left rows. The same condition in WHERE runs after, discarding rows whose padded columns are NULL — quietly turning a left join into an inner join.
- Why they ask: One of the most common real SQL bugs, so it's a favourite interview trap.
- What is the N+1 query problem?
- Fetching a list with one query, then issuing another query per row to load related data — 1 + N round trips where a single join or batched fetch would do. Usually introduced by an ORM's lazy loading.
You'll forget most of this by next week
That's not a discipline problem, it's how memory works. In the app these come back on an expanding schedule — right before you'd lose them.
Start free for 7 days