After this lesson you can ask "does a related row exist?" and "does none exist?" directly, without joining and without the traps of IN and NOT IN.
The shape of EXISTS
EXISTS takes a subquery and is true if that subquery returns at least one row. It is almost always written as a correlated subquery that looks for rows related to the current outer row. Which departments have at least one employee?
SELECT d.id, d.name
FROM departments d
WHERE EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
)
ORDER BY d.id;+----+-------------+
| id | name |
+----+-------------+
| 1 | Engineering |
| 2 | Sales |
| 3 | Marketing |
| 4 | Finance |
| 5 | Support |
+----+-------------+For each department, the inner query looks for an employee in it. If it finds one, EXISTS is true and the department is kept. The SELECT 1 is only a placeholder. The columns the subquery returns are ignored, so SELECT * and SELECT e.id would behave identically. Many people write SELECT 1 to make it obvious that no data is being fetched.
Research does not appear. It has no employees, so its inner query returns nothing.
NOT EXISTS: rows with no match
Add NOT and you keep the rows where the subquery finds nothing:
SELECT d.id, d.name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.id
);+----+----------+
| id | name |
+----+----------+
| 6 | Research |
+----+----------+This returns Research. Compare it with the NOT IN version from the subqueries lesson, which returned nothing at all because of the NULL in employees.department_id. NOT EXISTS is not affected. It does not build a list and compare against it; it asks, for each department, whether any matching row exists, and a NULL department never equals a department's id, so it simply never counts as a match.
That is why the usual advice is: to find rows without a match, use NOT EXISTS (or a LEFT JOIN ... IS NULL), and avoid NOT IN unless you are sure the list has no NULL.
Employees who are on no project
The same pattern works for people. Which employees have no assignment?
SELECT e.id, e.first_name, e.job_title
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM assignments a
WHERE a.employee_id = e.id
)
ORDER BY e.id;+----+------------+----------------------+
| id | first_name | job_title |
+----+------------+----------------------+
| 1 | Meera | Engineering Director |
| 15 | Pooja | Support Lead |
| 16 | Karan | Support Engineer |
| 17 | Sana | Support Engineer |
| 18 | Ravi | Consultant |
+----+------------+----------------------+These are the same five people you found with EXCEPT and with a left join. All three approaches are correct, and NOT EXISTS is often the one that states the intent most plainly: "employees for whom no assignment exists".
EXISTS with extra conditions
The inner query can have any filters. Who has worked more than 300 hours on at least one project?
SELECT e.id, e.first_name
FROM employees e
WHERE EXISTS (
SELECT 1
FROM assignments a
WHERE a.employee_id = e.id AND a.hours > 300
)
ORDER BY e.id;+----+------------+
| id | first_name |
+----+------------+
| 2 | Arjun |
| 3 | Kavya |
| 4 | Rohan |
| 7 | Neha |
| 8 | Sameer |
+----+------------+In this data nobody has two such assignments, but with a join an employee who did would appear once per matching assignment. EXISTS returns each employee once, however many matching rows they have, so you do not need DISTINCT. That is a real advantage when you only need to filter.
A subtler example
EXISTS can include joins in the inner query. Which employees work on a project owned by a different department than their own?
SELECT e.id, e.first_name, e.department_id
FROM employees e
WHERE EXISTS (
SELECT 1
FROM assignments a
INNER JOIN projects p ON a.project_id = p.id
WHERE a.employee_id = e.id
AND p.department_id <> e.department_id
);+----+------------+---------------+
| id | first_name | department_id |
+----+------------+---------------+
| 4 | Rohan | 1 |
+----+------------+---------------+Rohan (department 1) appears because he also works on Billing Migration, which belongs to Finance (department 4). Combining both EXISTS and NOT EXISTS in one query is also possible: "departments that own a project but have no employees", for example, would be EXISTS (projects) and NOT EXISTS (employees) together.
EXISTS versus IN
These two often give the same result:
SELECT first_name FROM employees
WHERE id IN (SELECT employee_id FROM assignments WHERE project_id = 5)
ORDER BY id;
SELECT first_name FROM employees e
WHERE EXISTS (SELECT 1 FROM assignments a WHERE a.employee_id = e.id AND a.project_id = 5)
ORDER BY id;+------------+
| first_name |
+------------+
| Rohan |
| Lata |
| Omar |
+------------+
+------------+
| first_name |
+------------+
| Rohan |
| Lata |
| Omar |
+------------+For positive tests, choose whichever reads better, and modern databases often optimise them in a similar way. The difference that matters is in the negative: NOT EXISTS is safe, NOT IN is fragile with NULL.
How to read an EXISTS query
A habit that helps: read the outer query as "keep each row for which...", then read the subquery as a plain sentence about that row. "Keep each department for which there is an employee whose department_id equals the department's id." If the sentence sounds right, the query probably is. If you cannot say it in one breath, the subquery is probably doing too much, and splitting it into a CTE may help. It also helps to run the inner query alone with a hard-coded id, for example department 6, to see what it returns before you wire it to the outer row.
Differences between databases
EXISTS and NOT EXISTS are standard and work in MySQL, PostgreSQL, SQLite and SQL Server. MySQL also has ANY and ALL forms, covered in EXISTS, ANY and ALL in MySQL. SQLite does not support ANY and ALL with a subquery, but EXISTS works everywhere.
Common mistakes
- Forgetting the correlation.
EXISTS (SELECT 1 FROM employees)without aWHERElinking it to the outer row is true for every row, as long as the table is not empty. - Using NOT IN with a nullable column. It can return nothing at all. Use
NOT EXISTS. - Selecting columns and expecting them to appear. Whatever the
EXISTSsubquery selects is thrown away. To show related data, join instead. - Using a join and DISTINCT just to filter.
EXISTSis clearer and avoids duplicates in the first place. - Reversing the condition. Write the link from inner to outer exactly as in a join,
a.employee_id = e.id.
Exercise
List the departments that own at least one project that has no end date yet (an ongoing project). Then list the departments that have employees but no project.
Show one possible answer
SELECT d.id, d.name
FROM departments d
WHERE EXISTS (
SELECT 1 FROM projects p
WHERE p.department_id = d.id AND p.end_date IS NULL
);
SELECT d.id, d.name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id)
AND NOT EXISTS (SELECT 1 FROM projects p WHERE p.department_id = d.id);+----+-------------+
| id | name |
+----+-------------+
| 1 | Engineering |
| 4 | Finance |
+----+-------------+
+----+---------+
| id | name |
+----+---------+
| 5 | Support |
+----+---------+Key points
- EXISTS is true when its subquery returns at least one row. What the subquery selects does not matter, so SELECT 1 is the convention.
- The subquery is normally correlated: it refers to the outer row and checks for related rows.
- NOT EXISTS keeps rows with no related rows. It is safe with NULLs, unlike NOT IN.
- EXISTS never duplicates outer rows, unlike a join, and the database can stop at the first match.
- EXISTS, a left join with IS NULL, and EXCEPT can all find rows with no match. Pick the clearest.