SQL JOINs Explained — INNER, LEFT, RIGHT, FULL, CROSS
The Interview Question
Explain the different types of SQL JOINs. When would you use each one?
Expert Answer
JOINs combine rows from two or more tables based on a related column. INNER JOIN returns only rows with matches in both tables — the most common and most restrictive. LEFT JOIN returns all rows from the left table and matching rows from the right; unmatched right-side columns are NULL. This is essential when you need all records from one table regardless of matches. RIGHT JOIN is the mirror image — all rows from the right table — and is rarely used in practice because you can just swap table order and use LEFT JOIN. FULL OUTER JOIN returns all rows from both tables, with NULLs where there's no match on either side — useful for finding records that exist in one table but not the other. CROSS JOIN produces the Cartesian product — every row from table A paired with every row from table B — used for generating combinations or date ranges. The critical performance note: JOINs should always be on indexed columns. Joining on non-indexed columns forces full table scans and kills query performance.
Key Points to Hit in Your Answer
- INNER JOIN: only matching rows (intersection)
- LEFT JOIN: all left rows + matching right rows (left table is preserved)
- RIGHT JOIN: all right rows + matching left rows (rarely used — just swap tables)
- FULL OUTER JOIN: all rows from both (union with NULLs)
- CROSS JOIN: every combination (Cartesian product) — rows multiply
- Always JOIN on indexed columns for performance
- Use LEFT JOIN + WHERE right.id IS NULL to find unmatched records
Code Example
-- Sample tables
-- users: id, name, department_id
-- departments: id, name
-- INNER JOIN: only users who have a department
SELECT u.name, d.name AS dept
FROM users u
INNER JOIN departments d ON u.department_id = d.id;
-- LEFT JOIN: all users, even those without a department
SELECT u.name, d.name AS dept
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;
-- Users without department show NULL for dept
-- Find users with no department (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN departments d ON u.department_id = d.id
WHERE d.id IS NULL;
-- CROSS JOIN: pair every user with every project
SELECT u.name, p.name
FROM users u
CROSS JOIN projects p;
-- 100 users × 10 projects = 1,000 rowsWhat Interviewers Are Really Looking For
Draw the Venn diagrams if you can. The anti-join pattern (LEFT JOIN + WHERE IS NULL) is an interview favorite. Know the difference between WHERE and ON in outer joins — filtering in WHERE can convert a LEFT JOIN into an INNER JOIN unintentionally. If asked about performance, mention that JOIN order can matter for the query optimizer and indexed columns are critical.
Practice This Question with AI Grading
Reading about interview questions is a start — but practicing with real-time AI feedback is how you actually get better. Goliath Prep grades your answers instantly and tells you exactly what you're missing.
Start Practicing Free →