How do INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN differ?
Take a moment.
Say your answer out loud or write it down first — retrieval is what makes it stick. Reveal when you're ready.
Short answer
INNER JOIN keeps only rows with a match on both sides. LEFT JOIN keeps every left row, filling NULLs where nothing matches. RIGHT JOIN is the mirror image. FULL JOIN keeps everything from both sides with NULLs for non-matches. Default to INNER; reach for LEFT when the right side is optional.
Detailed explanation
JOINs combine rows from two tables on a condition, usually equality of keys. INNER JOIN returns the intersection: a customer appears only if they have a matching order, an order only if its customer exists — orphans on either side vanish. LEFT JOIN preserves the entire left table: every customer appears at least once, with order columns NULL when they have no orders — the standard way to answer 'all X, plus their Y if any'. RIGHT JOIN is symmetric (preserve the right table); most teams rewrite it as a LEFT JOIN with the tables flipped for readability. FULL JOIN preserves both sides — mismatches from either table appear with NULLs — useful for reconciliation reports ('customers without orders AND orders without valid customers'). Practical notes: joining multiplies rows when the key isn't unique on one side (a fan-out that silently inflates COUNTs); filter placement matters (a WHERE on the right table after a LEFT JOIN effectively turns it back into an INNER JOIN — put such filters in the ON clause to keep the outer semantics); and CROSS JOIN (no condition) produces the cartesian product, occasionally intended, usually a missing ON clause bug.
Example
-- Customers and their orders, including customers with none
SELECT c.email, o.total_cents
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
-- Only customers that actually ordered (intersection)
SELECT c.email, o.total_cents
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;
-- ⚠️ WHERE on the nullable side kills the outer join:
-- This returns INNER JOIN results, not 'all customers':
-- ... LEFT JOIN orders o ON ... WHERE o.total_cents > 1000
-- Keep the filter in ON instead:
-- ... LEFT JOIN orders o ON o.customer_id = c.id AND o.total_cents > 1000Interview tip
Draw the Venn diagram verbally — intersection vs preserve-left — then volunteer the WHERE-vs-ON trap. That pitfall answer is what separates memorized definitions from real SQL experience.
Common mistakes
- Filtering the nullable side in WHERE and accidentally converting a LEFT JOIN to an INNER JOIN.
- Not realizing duplicate join keys fan out and inflate counts.