SQL EXISTS

Every query so far has lived entirely in one SELECT statement. A subquery is a query nested inside another one, and EXISTS is the simplest way to use one: it just asks whether a subquery returns any rows at all, true or false, without caring what those rows actually contain.

Learning Objectives

Write a WHERE EXISTS subquery and understand what it actually checks.
Use NOT EXISTS to find rows with no match.
Explain why EXISTS avoids the NOT IN / NULL gotcha from Module 2.

Outer Query

One row from departments at a time

Subquery

Does any employee match?

EXISTS

True or false, nothing else matters

Result

Only departments where it's true

Basic EXISTS

The inner query references d.id, a column from the outer query, that makes it a correlated subquery, it runs conceptually once per outer row, checking "does any employee have this specific department's id?" SELECT 1 is a common convention, EXISTS only cares whether any row comes back, not what's in it, so the selected value itself is irrelevant. SELECT * works exactly the same way here.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

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

NOT EXISTS

This finds departments with zero employees, Legal, in Beacon Robotics' data, the exact same question the LEFT JOIN + WHERE employees.id IS NULL pattern from Module 4 answered. Both are correct, EXISTS is often considered more readable for a pure "does a match exist" question, while LEFT JOIN is natural when the matched columns are also needed in the result.

MySQL / MariaDB / PostgreSQL / SQLite / SQL Server / Oracle

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

EXISTS vs IN vs LEFT JOIN

NOT INDanger: NULLs

Breaks silently if the list contains a NULL
Compares a value against a literal list or subquery result
Covered in Module 2's NOT IN gotcha

NOT EXISTSNULL-safe

Unaffected by NULLs in the subquery's results
Checks row existence, not specific values
Generally the safer default for "no match" questions

NOTE

This is the real, practical reason experienced developers often reach for NOT EXISTS over NOT IN once a subquery is involved: NOT IN's NULL gotcha from Module 2 simply doesn't apply to NOT EXISTS, since it never compares individual values, it only checks whether any row came back.

Try It Yourself

Write a query returning every department that has at least one employee earning over 90,000, using EXISTS.

Answer

SELECT name
FROM departments d
WHERE EXISTS (
  SELECT 1
  FROM employees e
  WHERE e.department_id = d.id
    AND e.salary > 90000
);

Frequently Asked Questions

Does it matter what's selected inside an EXISTS subquery?

No, EXISTS only checks whether any row is returned, not its contents. SELECT 1, SELECT *, and SELECT e.id all behave identically inside EXISTS.

Is every EXISTS subquery correlated?

Not necessarily, but it's by far the most common use. An uncorrelated EXISTS (one that doesn't reference the outer query) just checks a fixed yes/no condition for every outer row equally, rarely useful on its own.

Common Beginner Mistakes

Selecting specific columns inside EXISTS, expecting them to appear in the outer result

They never do, EXISTS discards everything the subquery returns except whether it returned anything at all. Select from the outer query if those columns are actually needed.

Forgetting the correlation, writing a subquery that doesn't reference the outer row

Without a WHERE e.department_id = d.id style link back to the outer query, EXISTS just evaluates the same fixed condition for every single outer row, true for all of them or false for all of them, not a per-row check.

Best Practices

Prefer EXISTS / NOT EXISTS over IN / NOT IN once a subquery (rather than a literal list) is involved, to avoid the NOT IN NULL trap entirely.
Use SELECT 1 by convention inside EXISTS, it signals to a reader that the selected columns don't matter.
Reach for a LEFT JOIN instead when the matched row's own columns are also needed in the output.

Interview Questions

What's the difference between EXISTS and IN?

IN compares a value against a literal list or a subquery's returned values directly, and inherits NOT IN's NULL-handling danger. EXISTS only checks whether a subquery returns any row at all, ignoring the actual values, which makes NOT EXISTS unaffected by NULLs in the subquery's results.

Write a query, using NOT EXISTS, that returns every department with zero employees.

SELECT name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id); — the correlated subquery checks, per department, whether any employee row points back to it; NOT EXISTS keeps only the departments where none do.

Summary

EXISTS checks whether a correlated subquery returns any rows, true or false, ignoring what those rows actually contain. NOT EXISTS answers "no match anywhere" questions the same way LEFT JOIN + IS NULL does, without NOT IN's NULL-handling danger.

What's Next?

ANY next, a different way to compare a value against a subquery's results, one row at a time instead of a simple true/false.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.