SQL Joins Interview Questions
Last updated 2026-11-19
Joins are the most reliably asked SQL topic. Interviewers rarely ask for definitions; they put two small tables on the screen and ask what a query returns, how many rows, or why a count is wrong. Each answer below shows the query and the result computed on the same sample data, so you can check your own prediction row by row.
All queries run on the two tables shown first. Gita has no department (NULL), and the Legal department has no employees. Those two rows are what make the join types behave differently, so keep them in mind. For the wider set see SQL interview questions and the strategy in how to crack a SQL interview.
| Dialects | Standard SQL; MySQL and PostgreSQL differences noted |
|---|---|
| Sample tables | employees (7 rows), departments (4 rows) |
| Level | Easy to hard |
Sample data used on this page
Two tables. employees has a manager_id pointing back at emp_id for the self-join questions. Asha has no manager, and Gita has no department.
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(20), dept_id INT, salary INT, manager_id INT);
INSERT INTO departments VALUES (10,'Sales'),(20,'Engineering'),(30,'HR'),(40,'Legal');
INSERT INTO employees VALUES
(1,'Asha',20,90000,NULL),(2,'Bilal',20,75000,1),(3,'Chitra',10,60000,1),
(4,'Dev',10,60000,3),(5,'Esha',30,50000,1),(6,'Farid',20,75000,1),(7,'Gita',NULL,40000,5);What to look for in any join question
- Which side is preserved: LEFT keeps every left row, even if nothing matches.
- Where a filter sits: in ON it limits matching, in WHERE it removes result rows after the join.
- NULL never equals NULL, so NULL keys do not match each other.
- One-to-many joins multiply rows, which inflates SUM and COUNT.
SQL Joins Interview Questions: questions with worked answers
1 What does INNER JOIN return on this data?
easyOnly rows with a match in both tables. Gita (NULL department) and Legal (no employees) disappear, so six rows are returned.
sqlSELECT e.name, d.dept_name FROM employees e INNER JOIN departments d ON e.dept_id = d.dept_id ORDER BY e.emp_id; -- name | dept_name -- -------+------------ -- Asha | Engineering -- Bilal | Engineering -- Chitra | Sales -- Dev | Sales -- Esha | HR -- Farid | Engineering2 What does LEFT JOIN return, and how do you read the unmatched rows?
easyEvery row from the left table, with NULLs for right-side columns where no match exists. Gita appears with a NULL department name. All seven employees are returned, whereas the right-hand Legal row is not, because it has no employee.
sqlSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id ORDER BY e.emp_id; -- name | dept_name -- -------+------------ -- Asha | Engineering -- Bilal | Engineering -- Chitra | Sales -- Dev | Sales -- Esha | HR -- Farid | Engineering -- Gita | NULL3 Find departments that have no employees (an anti-join). Show three ways.
mediumThe LEFT JOIN with IS NULL check keeps departments whose join found nothing. NOT EXISTS is usually the clearest and handles NULLs correctly. NOT IN is dangerous: if the subquery returns any NULL, the whole predicate becomes unknown and returns no rows. Here employees.dept_id contains a NULL (Gita), so NOT IN returns an empty result even though Legal has no employees.
sqlSELECT d.dept_name FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id WHERE e.emp_id IS NULL; -- dept_name -- --------- -- LegalsqlSELECT d.dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id); -- dept_name -- --------- -- LegalsqlSELECT d.dept_name FROM departments d WHERE d.dept_id NOT IN (SELECT dept_id FROM employees); -- (0 rows)4 What is the difference between putting a condition in ON versus WHERE with a LEFT JOIN?
mediumA condition in ON decides which right-side rows match, so unmatched left rows still appear with NULLs. The same condition in WHERE filters the final rows, and because NULL fails the test it removes the unmatched rows, silently turning the LEFT JOIN into an inner join. Compare the two results: the first keeps all seven employees, the second only the Engineering ones.
sqlSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = 'Engineering' ORDER BY e.emp_id; -- name | dept_name -- -------+------------ -- Asha | Engineering -- Bilal | Engineering -- Chitra | NULL -- Dev | NULL -- Esha | NULL -- Farid | Engineering -- Gita | NULLsqlSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id WHERE d.dept_name = 'Engineering' ORDER BY e.emp_id; -- name | dept_name -- ------+------------ -- Asha | Engineering -- Bilal | Engineering -- Farid | Engineering5 How do you write a FULL OUTER JOIN in MySQL, which does not support it?
mediumPostgreSQL, SQL Server, Oracle and SQLite (3.39+) support FULL OUTER JOIN directly. MySQL does not, so combine a LEFT JOIN and a RIGHT JOIN with UNION, which removes the duplicate matched rows (use UNION ALL plus an anti-join condition if duplicates matter). The result has both unmatched sides: Gita with no department and Legal with no employee.
sqlSELECT e.name, d.dept_name FROM employees e LEFT JOIN departments d ON e.dept_id = d.dept_id UNION SELECT e.name, d.dept_name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.dept_id ORDER BY 1; -- Equivalent in PostgreSQL: FROM employees e FULL OUTER JOIN departments d ON ... -- name | dept_name -- -------+------------ -- NULL | Legal -- Asha | Engineering -- Bilal | Engineering -- Chitra | Sales -- Dev | Sales -- Esha | HR -- Farid | Engineering -- Gita | NULL6 Write a self join to list each employee with their manager.
mediumJoin the table to itself with two aliases, matching manager_id to emp_id. Use LEFT JOIN so the top of the hierarchy (Asha, with no manager) is not dropped. Always alias both sides, otherwise the column names are ambiguous.
sqlSELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id ORDER BY e.emp_id; -- employee | manager -- ---------+-------- -- Asha | NULL -- Bilal | Asha -- Chitra | Asha -- Dev | Chitra -- Esha | Asha -- Farid | Asha -- Gita | Esha7 Why can a join make COUNT or SUM wrong? Show the fan-out.
mediumJoining a parent to a one-to-many child repeats the parent row once per child, so any aggregate over parent columns is inflated; aggregate the child table first or count distinct keys. A second trap is that COUNT(*) also counts the NULL-extended row a LEFT JOIN creates for an unmatched parent: Legal has no employees, yet COUNT(*) reports 1, while COUNT(e.emp_id) correctly reports 0.
sqlSELECT d.dept_name, COUNT(*) AS wrong, COUNT(e.emp_id) AS right_count FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_name ORDER BY d.dept_name; -- dept_name | wrong | right_count -- ------------+-------+------------ -- Engineering | 3 | 3 -- HR | 1 | 1 -- Legal | 1 | 0 -- Sales | 2 | 28 What does a CROSS JOIN produce and how many rows?
easyThe Cartesian product: every row of the first table paired with every row of the second, so m times n rows, here 7 x 4 = 28. It has no join condition. It is used deliberately to generate combinations (all dates times all products), and accidentally when someone forgets the ON clause in an old comma-style join.
sqlSELECT COUNT(*) AS rows_returned FROM employees CROSS JOIN departments; -- rows_returned -- ------------- -- 289 What is the difference between ON, USING and NATURAL JOIN?
mediumON takes any condition. USING(col) joins on a column with the same name in both tables and outputs that column once. NATURAL JOIN joins on every column that happens to share a name, which is fragile: adding an innocently named column later (like both tables getting a last_updated column) silently changes the query. Avoid NATURAL JOIN in production code. In MySQL and PostgreSQL USING works the same way, and Oracle also supports it.
sqlSELECT name, dept_name FROM employees JOIN departments USING (dept_id) ORDER BY emp_id; -- name | dept_name -- -------+------------ -- Asha | Engineering -- Bilal | Engineering -- Chitra | Sales -- Dev | Sales -- Esha | HR -- Farid | Engineering10 Do NULL keys match in a join? How do you join on a nullable column?
mediumNo. NULL = NULL evaluates to unknown, so rows with NULL keys never match in an inner or outer join condition, which is why Gita matches no department. If you really want NULLs to match, use the null-safe operator: IS NOT DISTINCT FROM in PostgreSQL, or the <=> operator in MySQL. Think carefully, because matching NULLs usually means the data model is wrong.
11 Find employees who earn more than their manager.
hardSelf join with a comparison on salary. Here Bilal earns 75000 and his manager Asha earns 90000, so nobody qualifies, and the result is empty. Change the data and rows appear, which is the point of testing with an empty case. A correlated subquery is the alternative.
sqlSELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE e.salary > m.salary; -- (0 rows)12 Join three tables: list each department with its employee count and total salary, including empty departments.
mediumStart from the table that must be preserved (departments), LEFT JOIN employees, then group. Use COUNT(e.emp_id) and COALESCE on SUM so empty departments show 0 rather than NULL. Employees without a department are excluded because they have no department row to attach to.
sqlSELECT d.dept_name, COUNT(e.emp_id) AS headcount, COALESCE(SUM(e.salary), 0) AS total_salary FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_name ORDER BY d.dept_name; -- dept_name | headcount | total_salary -- ------------+-----------+------------- -- Engineering | 3 | 240000 -- HR | 1 | 50000 -- Legal | 0 | 0 -- Sales | 2 | 12000013 What is a non-equi join? Give an example.
hardA join whose condition uses an operator other than equality: range, inequality or BETWEEN. Typical uses are salary bands, date ranges and pairs without duplicates. This finds each pair of employees in one department where the first earns more than the second. It cannot use a hash join, so it is usually executed as a nested loop and can be slow on large tables.
sqlSELECT a.name AS higher, b.name AS lower FROM employees a JOIN employees b ON a.dept_id = b.dept_id AND a.salary > b.salary ORDER BY a.name, b.name; -- higher | lower -- -------+------ -- Asha | Bilal -- Asha | Farid14 How does the database execute a join? Name the join algorithms.
mediumThree main strategies. Nested loop: for each row of the outer input, probe the inner input, best with an index on the inner join key and small outer sets. Hash join: build a hash table on the smaller input and probe it with the other, good for large unsorted equi-joins (PostgreSQL has it; MySQL added it in 8.0.18). Merge join: both inputs sorted on the key and merged, good when sorted or indexed already. Use EXPLAIN to see which is chosen. The optimiser picks by estimated cost, so up-to-date statistics matter. See SQL indexes questions.
15 What is a semi join, and when do EXISTS and IN beat a join?
mediumA semi join returns rows from the left table that have at least one match on the right, without duplicating them. EXISTS and IN express it directly. An inner join that returns the same left row once per match gives duplicates that you would need DISTINCT to remove. Departments that have at least one employee:
sqlSELECT d.dept_name FROM departments d WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id) ORDER BY d.dept_name; -- dept_name -- ----------- -- Engineering -- HR -- Sales
Frequently asked questions
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN keeps only rows with matches on both sides. LEFT JOIN keeps every left row and fills the right-side columns with NULL when there is no match.
Does join order matter?
For inner joins the optimiser can reorder them freely. For outer joins the order and the preserved side change the result.
Why does my LEFT JOIN behave like an INNER JOIN?
A WHERE condition on a right-table column removes the NULL rows. Move the condition into ON, or add OR column IS NULL.
Where can I practise more SQL queries?
Try SQL query practice and SQL for freshers.
Related guides
More in Programming Language Interview Questions by Language
- SQL Query Practice: Interview Questions with Solutions
- SQL Subqueries Interview Questions
- SQL Window Functions Interview Questions
- TypeScript Interview Questions and Answers
- .NET Core and .NET 8 Interview Questions
- Advanced Python Interview Questions
- ASP.NET Core MVC Interview Questions
- C Language Interview Questions and Answers
- C Pointers Interview Questions
- C Programming Output Questions with Answers
- C# Async Await Interview Questions
- C# Coding Interview Questions