SQL JOINs Interview Questions
What Interviewers Are Testing
SQL JOIN interview questions test your ability to combine data from multiple tables and understand how different join types affect the result set.
Interview Questions & Model Answers
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows where there is a matching record in BOTH tables. LEFT JOIN returns ALL rows from the left table, and the matching rows from the right table. If no match is found in the right table, NULL is returned for all right-table columns.
-- INNER JOIN: only matched employees SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.dept_id = d.id; -- LEFT JOIN: all employees, even if no department SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.dept_id = d.id;
Potential Follow-up Questions:
- When would you use a LEFT JOIN over INNER JOIN?
- How do you find records that exist in one table but not another?
What is a SELF JOIN and when would you use it?
A SELF JOIN joins a table with itself using two different aliases. It's used for hierarchical data — for example, finding an employee's manager when both employees and managers are in the same table.
-- Employee-manager hierarchy
SELECT e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;Potential Follow-up Questions:
- What's the difference between a SELF JOIN and a recursive CTE?
What is a CROSS JOIN and when is it useful?
A CROSS JOIN returns the Cartesian product of two tables — every combination of every row from both tables. If table A has 5 rows and table B has 4 rows, CROSS JOIN returns 20 rows. Use cases: generating all possible combinations (e.g., size × color for a product catalog), creating calendar dimension tables by crossing a numbers table.
-- Generate all size-color combinations SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;
Potential Follow-up Questions:
- What happens if you accidentally write a JOIN without an ON clause?
How do you find records in Table A that do NOT exist in Table B?
Use a LEFT JOIN and filter for NULL in the right table's column (anti-join pattern). Alternatively, use NOT EXISTS or NOT IN with a subquery.
-- Method 1: LEFT JOIN anti-join SELECT a.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id WHERE b.a_id IS NULL; -- Method 2: NOT EXISTS SELECT * FROM table_a a WHERE NOT EXISTS ( SELECT 1 FROM table_b b WHERE b.a_id = a.id );
Potential Follow-up Questions:
- Which approach (LEFT JOIN vs NOT EXISTS) is more performant?
What is the difference between UNION and JOIN?
JOIN combines columns from two tables horizontally (more columns per row). UNION combines rows from two queries vertically (more rows, same column structure). JOIN requires a related column; UNION requires the same number and compatible types of columns in both SELECT statements.
-- JOIN: horizontal combination SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id; -- UNION: vertical combination SELECT name FROM current_employees UNION SELECT name FROM former_employees;
Potential Follow-up Questions:
- What is the difference between UNION and UNION ALL?
Frequently Asked Questions
What SQL JOIN questions are asked in interviews?
How do you explain SQL JOINs in a technical interview?
Related Interview Topics
Practice Answering Live
Use our Interview Arena to practice SQL challenges under real interview conditions. Free.