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.
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
LEFTJOIN departments d ON d.id = e.department_id
ORDERBY e.id
full_name
department
Mara Lind
Engineering
Joel Park
Engineering
Ines Cole
Engineering
Tomas Reyes
Sales
Nadia Voss
Sales
Owen Hart
Sales
Pia Novak
Support
Sven Ito
Support
Lea Brandt
NULL
Ravi Shah
Engineering
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
WHERENOTEXISTS (SELECT1FROM employees e WHERE e.department_id = d.id)
name
Finance
Result: 1 row
SELECT d.name
FROM departments d
LEFTJOIN employees e ON e.department_id = d.id
WHERE e.id ISNULL
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
LEFTJOIN employees m ON m.id = e.manager_id
ORDERBY e.id
employee
manager
Mara Lind
NULL
Joel Park
Mara Lind
Ines Cole
Mara Lind
Tomas Reyes
Nadia Voss
Nadia Voss
NULL
Owen Hart
Nadia Voss
Pia Novak
Sven Ito
Sven Ito
NULL
Lea Brandt
NULL
Ravi Shah
Mara 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_name
salary
manager
manager_salary
Tomas Reyes
6900
Nadia Voss
6400
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
LEFTJOIN employees e ON e.department_id = d.id
GROUPBY d.name
ORDERBY d.name
name
headcount
Engineering
4
Finance
0
Sales
3
Support
2
Result: 4 rows · correct
SELECT d.name, COUNT(*) AS headcount
FROM departments d
LEFTJOIN employees e ON e.department_id = d.id
GROUPBY d.name
ORDERBY d.name
name
headcount
Engineering
4
Finance
1
Sales
3
Support
2
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.
SELECTSUM(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
SELECTSUM(budget) AS correct
FROM departments
WHERE id IN (SELECT department_id FROM employees)
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
LEFTJOIN departments d ON d.id = e.department_id AND d.name = 'Support'ORDERBY e.id
full_name
department
Mara Lind
NULL
Joel Park
NULL
Ines Cole
NULL
Tomas Reyes
NULL
Nadia Voss
NULL
Owen Hart
NULL
Pia Novak
Support
Sven Ito
Support
Lea Brandt
NULL
Ravi Shah
NULL
Result: 10 rows · 10 rows; only the two Support people have a department
SELECT e.full_name, d.name AS department
FROM employees e
LEFTJOIN departments d ON d.id = e.department_id
WHERE d.name = 'Support'ORDERBY e.id
full_name
department
Pia Novak
Support
Sven Ito
Support
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.
SELECTCOUNT(*) AS cross_rows FROM employees, departments
cross_rows
40
Result: 1 row
SELECTCOUNT(*) 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 newerSELECT d.name, e.full_name
FROM departments d
FULLJOIN 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
LEFTJOIN employees e ON e.department_id = d.id
UNIONALLSELECT d.name, e.full_name
FROM employees e
LEFTJOIN departments d ON d.id = e.department_id
WHERE d.id ISNULLORDERBY department, employee
department
employee
NULL
Lea Brandt
Engineering
Ines Cole
Engineering
Joel Park
Engineering
Mara Lind
Engineering
Ravi Shah
Finance
NULL
Sales
Nadia Voss
Sales
Owen Hart
Sales
Tomas Reyes
Support
Pia Novak
Support
Sven Ito
Result: 11 rows · NULL sorts first, so Lea's row is on top
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.