Query Belt Coming to Google Play

SQL JOIN interview questions

Eight JOIN questions that test whether you understand what a join does to your rows, with queries, real results and the mistake behind each trap.

By Andrii Boiko · Updated 2026-11-18 · Every result on this page was produced by running the query on the sample database below in SQLite 3.37.2.

The sample database

All examples use three small tables with made-up data. Note the traps built into them: one employee has no department, two employees have the same salary, one department has no employees, and one sale has no amount.

departments(id, name, budget)
employees(id, full_name, department_id, manager_id, salary, hired_on)
sales(id, sold_on, region, amount)

departments

idnamebudget
1Sales30000
2Engineering80000
3Support20000
4Finance15000

employees

idfull_namedepartment_idmanager_idsalaryhired_on
1Mara Lind2NULL92002019-03-04
2Joel Park2176002020-06-15
3Ines Cole2176002021-01-11
4Tomas Reyes1569002018-09-01
5Nadia Voss1NULL64002017-05-22
6Owen Hart1543002022-02-14
7Pia Novak3839002023-04-03
8Sven Ito3NULL52002020-11-30
9Lea BrandtNULLNULL48002024-01-08
10Ravi Shah2161002022-08-19

sales

idsold_onregionamount
12026-01-01North120
22026-01-02South80
32026-01-03North150
42026-01-04SouthNULL
52026-01-05North90
62026-01-06South200
72026-01-07North110

List every employee with their department name, including people with no department.

Use a LEFT JOIN from employees, because you want to keep every employee. Employees without a department get NULL in the department column. An inner join would silently drop Lea.

SELECT e.full_name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
ORDER BY e.id
full_namedepartment
Mara LindEngineering
Joel ParkEngineering
Ines ColeEngineering
Tomas ReyesSales
Nadia VossSales
Owen HartSales
Pia NovakSupport
Sven ItoSupport
Lea BrandtNULL
Ravi ShahEngineering
Result: 10 rows

Which departments have no employees?

This is an anti-join: rows in one table with no match in another. Two reliable ways: NOT EXISTS, or a LEFT JOIN followed by WHERE right.key IS NULL. Both give the same answer. Avoid NOT IN here: the employee list contains a NULL department, which makes NOT IN return nothing (see the NOT IN trap).

SELECT d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id)
name
Finance
Result: 1 row
SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL
name
Finance
Result: 1 row

Show each employee with their manager's name. Then find employees who earn more than their manager.

Join the table to itself with two aliases. Use LEFT JOIN if people without a manager must stay in the list. For the second part, compare the two salaries in WHERE.

SELECT e.full_name AS employee, m.full_name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id
employeemanager
Mara LindNULL
Joel ParkMara Lind
Ines ColeMara Lind
Tomas ReyesNadia Voss
Nadia VossNULL
Owen HartNadia Voss
Pia NovakSven Ito
Sven ItoNULL
Lea BrandtNULL
Ravi ShahMara Lind
Result: 10 rows
SELECT e.full_name, e.salary, m.full_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary
full_namesalarymanagermanager_salary
Tomas Reyes6900Nadia Voss6400
Result: 1 row

Count employees per department, including departments with none.

Start from departments with a LEFT JOIN, and count a column from the right table, not *. COUNT(*) counts the row that the LEFT JOIN made for Finance, so it reports 1 instead of 0.

SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name
nameheadcount
Engineering4
Finance0
Sales3
Support2
Result: 4 rows · correct
SELECT d.name, COUNT(*) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name
nameheadcount
Engineering4
Finance1
Sales3
Support2
Result: 4 rows · wrong: Finance shows 1

I joined departments to employees and summed the budget. Why is the total so big?

A join repeats the "one" side once for every matching row on the "many" side. Each department's budget is now counted once per employee. Sum before you join, or sum over the table that already has one row per department.

SELECT SUM(d.budget) AS inflated
FROM departments d
JOIN employees e ON e.department_id = d.id
inflated
450000
Result: 1 row · wrong: each budget counted once per employee
SELECT SUM(budget) AS correct
FROM departments
WHERE id IN (SELECT department_id FROM employees)
correct
130000
Result: 1 row · Sales 30000 + Engineering 80000 + Support 20000

Whenever a number looks too large after a join, count the rows before and after the join. If they grew, you have a fan-out.

What is the difference between putting a condition in ON and in WHERE for a LEFT JOIN?

A condition in ON decides which rows match; unmatched left rows are still kept. A condition on the right table in WHERE runs after the join and throws away the NULL rows, so the LEFT JOIN behaves like an inner join. If you want all employees but department names only for Support, the condition belongs in ON.

SELECT e.full_name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id AND d.name = 'Support'
ORDER BY e.id
full_namedepartment
Mara LindNULL
Joel ParkNULL
Ines ColeNULL
Tomas ReyesNULL
Nadia VossNULL
Owen HartNULL
Pia NovakSupport
Sven ItoSupport
Lea BrandtNULL
Ravi ShahNULL
Result: 10 rows · 10 rows; only the two Support people have a department
SELECT e.full_name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Support'
ORDER BY e.id
full_namedepartment
Pia NovakSupport
Sven ItoSupport
Result: 2 rows · 2 rows: the LEFT JOIN became an inner join

A query returns far too many rows after I forgot the ON clause. What happened?

Without a join condition you get a cross join: every row of one table paired with every row of the other. Ten employees and four departments make 40 rows. A quick sanity check for any join is to compare the row count with what you expect.

SELECT COUNT(*) AS cross_rows FROM employees, departments
cross_rows
40
Result: 1 row
SELECT COUNT(*) AS joined_rows FROM employees e JOIN departments d ON d.id = e.department_id
joined_rows
9
Result: 1 row

How do you write a FULL OUTER JOIN in SQLite?

SQLite supports RIGHT JOIN and FULL JOIN only from version 3.39 (2022). In older versions, and in interviews where you are asked to show you understand them, build it from a LEFT JOIN plus the unmatched rows of the other side. The result below has 11 rows: 9 matches, Finance with no employees, and Lea with no department.

-- SQLite 3.39 and newer
SELECT d.name, e.full_name
FROM departments d
FULL JOIN employees e ON e.department_id = d.id;

Portable version, run here:

SELECT d.name AS department, e.full_name AS employee
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
UNION ALL
SELECT d.name, e.full_name
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE d.id IS NULL
ORDER BY department, employee
departmentemployee
NULLLea Brandt
EngineeringInes Cole
EngineeringJoel Park
EngineeringMara Lind
EngineeringRavi Shah
FinanceNULL
SalesNadia Voss
SalesOwen Hart
SalesTomas Reyes
SupportPia Novak
SupportSven Ito
Result: 11 rows · NULL sorts first, so Lea's row is on top

Keep going

Practice it, don't just read it. The orange belt in Query Belt is all JOINs: INNER, LEFT, self, anti-join, with a row-by-row view that tells you when a JOIN duplicated or dropped rows.

Coming to Google Play