Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 30 of 49

EXISTS and NOT EXISTS

Test whether related rows exist with EXISTS and NOT EXISTS, find rows with no match safely, and learn why NOT EXISTS beats NOT IN when NULLs are present.

14 minSubqueries and CTEsUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonUse EXISTS and NOT EXISTS to check for related rows, and choose NOT EXISTS over NOT IN when NULL values may be present.

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?

sql
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;
result
+----+-------------+
| 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:

sql
SELECT d.id, d.name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
);
result
+----+----------+
| 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?

sql
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;
result
+----+------------+----------------------+
| 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?

sql
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;
result
+----+------------+
| 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?

sql
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
);
result
+----+------------+---------------+
| 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:

sql
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;
result
+------------+
| 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 a WHERE linking 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 EXISTS subquery selects is thrown away. To show related data, join instead.
  • Using a join and DISTINCT just to filter. EXISTS is 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
sql
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);
result
+----+-------------+
| 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.

Check yourself

1. What does EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id) check?
2. Why is NOT EXISTS safer than NOT IN for finding departments without employees?
3. What does the SELECT list inside an EXISTS subquery decide?
4. A department has three employees. Does WHERE EXISTS (...) return the department once or three times?