How to Find Departments with No Employees in SQL?

Three ways to find the "missing" rows, and the NULL trap that makes one of them quietly return nothing

Most SQL questions ask you to find rows that exist. This one asks you to find rows that don't have a match: which departments have no employees? It is a classic interview question, and it is also a very practical one. Empty departments, customers who never ordered, and products that were never sold are all the same pattern. In this post, we'll set up the tables, build the query, compare three approaches, and look at the NULL trap that catches even experienced developers.

The problem

You have a departments table and an employees table linked by department_id. Return every department that has no employees.

Step 1: Create the tables

CREATE TABLE departments (
  id   INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
  id            INT PRIMARY KEY,
  name          VARCHAR(50) NOT NULL,
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

Note that department_id in employees allows NULL, meaning an employee can exist without being assigned to a department. That detail becomes important later.

Step 2: Insert sample data

INSERT INTO departments (id, name) VALUES
(10, 'Engineering'),
(20, 'Sales'),
(30, 'HR'),
(40, 'Finance'),
(50, 'Legal');

INSERT INTO employees (id, name, department_id) VALUES
(1, 'Asha',  10),
(2, 'Ravi',  10),
(3, 'Meera', 20),
(4, 'Kiran', 30),
(5, 'Tara',  NULL);

What the data looks like

Department	Employees
Engineering (10)	Asha, Ravi
Sales (20)	Meera
HR (30)	Kiran
Finance (40)	none
Legal (50)	none

Tara has no department at all, so she doesn't belong to any row in the table above. The answer we expect is Finance and Legal.

Approach 1: LEFT JOIN with IS NULL

SELECT d.id, d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;

How it works

1. LEFT JOIN keeps every department from the left table, even when no employee matches it.
2. For a department with no employees, all the e.* columns come back as NULL.
3. WHERE e.id IS NULL keeps only those unmatched departments.

Result

id	name
40	Finance
50	Legal

A useful way to picture it: first the join produces one row per department-employee pair, plus one row with blanks for each department that has nobody. The filter then keeps just the rows with blanks.

Approach 2: NOT EXISTS

SELECT d.id, d.name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);

How it works

For each department, the subquery looks for at least one employee whose department_id matches. If none is found, the department passes the filter. The result is the same two rows: Finance and Legal.

This version reads closest to the question itself, "departments for which no employee exists," and the database can stop searching a department as soon as it finds the first matching employee. The SELECT 1 is just a convention; the subquery only needs to know whether a row exists, not what it contains.

Approach 3: NOT IN (handle with care)

SELECT id, name
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees);

This looks perfectly reasonable, and on tidy data it works. But run it against our sample data and it returns no rows at all, which is wrong.

Why it fails: the NULL trap

Tara's department_id is NULL, so the subquery produces the list 10, 10, 20, 30, NULL. In SQL, comparing any value to NULL gives unknown, not true or false. A condition like id NOT IN (10, 10, 20, 30, NULL) can therefore never be true for any department, and every row is rejected.

There are two ways to fix it:

• Remove the NULLs from the subquery: WHERE department_id IS NOT NULL.
• Use NOT EXISTS or the LEFT JOIN version, which don't have this problem.

SELECT id, name
FROM departments
WHERE id NOT IN (
  SELECT department_id
  FROM employees
  WHERE department_id IS NOT NULL
);

With the NULLs filtered out, this version returns Finance and Legal as expected.

Which approach should you choose?

Approach	Safe with NULLs?	Best for
LEFT JOIN ... IS NULL	Yes	The classic answer; easy to extend with extra columns
NOT EXISTS	Yes	Reads like the question; can stop early per row
NOT IN	Only if the subquery excludes NULLs	Quick checks on columns that are NOT NULL

LEFT JOIN and NOT EXISTS give the same result on this data, and most modern databases optimize them similarly. Prefer either of those, and reserve NOT IN for subquery columns that are guaranteed never to be NULL.

A related report: headcount for every department

Sometimes you don't just want the empty departments; you want every department with its employee count, including zeros.

SELECT d.name, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY employee_count, d.name;

name	employee_count
Finance	0
Legal	0
HR	1
Sales	1
Engineering	2

Why COUNT(e.id) and not COUNT(*)?

COUNT(e.id) skips NULL values, so a department with no employees counts 0. COUNT(*) counts rows, and the LEFT JOIN produces one row of blanks for each empty department, so it would wrongly report 1. This small difference is one of the most common mistakes in reports built on outer joins.

Final thoughts

Finding rows with no match is really a question about what is missing, and SQL answers it by joining first and then keeping the gaps. Remember the three rules from this post: use LEFT JOIN ... IS NULL or NOT EXISTS as your default, treat NOT IN with suspicion whenever NULL might be involved, and count a column from the right-hand table, not *, when you need zeros to show up. Get those right, and the same pattern will answer a whole family of "who never did X?" questions.