SQL · Queries that survive scale
How do joins work in SQL?
Short answer
A join combines rows from two inputs using a predicate. INNER JOIN keeps matches, LEFT or RIGHT JOIN preserves one side, FULL JOIN preserves both, and CROSS JOIN produces every combination.
Joins connect tables through keys, usually a foreign key to a primary key. The join condition determines which row pairs combine. Without a condition, most databases produce a Cartesian product, which is rarely intended.
SELECT o.id, o.total_cents, c.email
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days';Join types
- INNER JOIN: only rows with a match on both sides
- LEFT JOIN: every left row, with NULLs when no match exists
- RIGHT JOIN: every right row, mirrored from LEFT JOIN
- FULL OUTER JOIN: rows from both sides, with NULLs where either is missing
- CROSS JOIN: every combination, useful for generating grids or test data
Most engines execute joins with nested loops, hash joins, or merge joins. The planner chooses based on row estimates, indexes, and sort order. A small input with an indexed lookup often uses nested loops; two large unsorted inputs often use a hash join.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1What is the difference between a join condition and a WHERE filter?
- 2When would a hash join be chosen over a nested loop?
- 3What happens if you forget the ON clause?
- 4How does a many-to-many relationship work?
Say it in the interview
A join combines rows from two inputs based on a predicate, normally a foreign key relationship. INNER JOIN keeps only matches, while LEFT JOIN keeps every row from one side and fills NULLs when there is no match. The planner picks nested loop, hash, or merge join based on cardinality and indexes. I check that join columns are indexed and that the condition is not accidentally producing a Cartesian product.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.