IN, NOT IN, EXISTS and NOT EXISTS

Scalar subqueries answer 'what is the average?' — a single number. But many questions are about membership: 'which employees work in Oslo?' or 'which departments have no staff?' Those need a subquery that returns a set of values, not just one.

SQL provides four operators for this. IN and NOT IN test whether a value appears inside a list or subquery result. EXISTS and NOT EXISTS test whether a subquery returns any rows at all, without caring what the actual values are.

All four are more powerful than a scalar subquery because they embrace many rows, but they come with their own rules and traps — especially when NULL appears.

flowchart LR
  V["Value 3"] -->|"IN (1,2,3)"| T["TRUE"]
  V2["Value 5"] -->|"IN (1,2,3)"| F["FALSE"]
  Q["Subquery returns rows?"] -->|"yes"| E["EXISTS = TRUE"]
  Q -->|"no"| NE["EXISTS = FALSE"]
  style T fill:#0e7490,color:#fff
  style E fill:#0e7490,color:#fff
  style F fill:#b45309,color:#fff

IN checks membership in a set; EXISTS checks whether a set is non-empty.

IN and NOT IN — membership tests

IN asks whether a value is found inside a subquery's result set. NOT IN asks the opposite. They read almost like English: WHERE department_id IN (SELECT id FROM departments WHERE city = 'Oslo') keeps every employee whose department is in the Oslo set.

The subquery can return any number of rows, but it must return exactly one column — the column type must match what you are comparing against. If the subquery returns zero rows, IN is always false and NOT IN is always true for that value.

Because the database can often turn an IN subquery into an efficient join behind the scenes, IN is usually the most readable way to express a membership test.

Employees who work in departments located in Oslo, using IN.

SELECT name, role
FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE city = 'Oslo');

EXISTS and NOT EXISTS — existence tests

EXISTS checks whether a subquery returns one or more rows. It does not inspect the values inside those rows; it only cares that the set is non-empty. This makes it ideal for questions like 'which departments have at least one active project?' where the answer is yes or no.

The standard idiom is SELECT 1 inside an EXISTS subquery — the literal 1 is a placeholder because EXISTS never looks at the column values anyway.

NOT EXISTS flips the logic: it is true only when the subquery returns zero rows. 'Which departments have no employees?' becomes a NOT EXISTS question.

Departments that currently have no employees, using NOT EXISTS.

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

flowchart TD
  subgraph IN["NOT IN approach"]
    S1["Build full list of ids"] --> M1["Check membership"]
  end
  subgraph EX["NOT EXISTS approach"]
    S2["Find first match> stop"] --> M2["Return false immediately"]
  end
  style EX fill:#0e7490,color:#fff

EXISTS stops as soon as it finds the first matching row; NOT IN must build the whole set first.

Choosing between IN and EXISTS

For many questions either one works, but there are practical differences:

IN is more intuitive when the subquery is a simple list of values and you want to ask 'is my value in this list?'
EXISTS is safer when NULLs might appear in the subquery result, and it can stop at the first match instead of building the whole list.
EXISTS also pairs naturally with correlated subqueries — the topic of the next lesson — because it can reference outer columns in its WHERE clause.

As a rule of thumb: prefer IN for fixed lookup lists, and EXISTS when the test depends on outer-query data or when NULLs are possible.

Employees in departments that have at least one active project, using EXISTS.

SELECT e.name
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM projects p
  WHERE p.department_id = e.department_id
    AND p.status = 'active'
);

flowchart TD
  Q["Need to test membership?"] -->|"fixed list of values"| IN["Use IN"]
  Q -->|"may contain NULL"| EX["Use EXISTS"]
  Q -->|"correlated with outer row"| EX2["Use EXISTS"]
  style IN fill:#0e7490,color:#fff
  style EX fill:#0e7490,color:#fff

Choose IN for simple value lists, EXISTS when NULLs may appear or when correlating with the outer query.

Exercise

List the name and role of every employee who works in a department located in 'Oslo'. Use IN with a subquery against the departments table.

SELECT name, role
FROM employees
WHERE department_id IN -- complete the subquery
;

SELECT name, role FROM employees WHERE department_id IN (SELECT id FROM departments WHERE city = 'Oslo');

Exercise

Return the name of every department that has no employee earning more than 950000. Use NOT EXISTS.

SELECT d.name
FROM departments d
WHERE NOT EXISTS -- add the subquery
;

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

Exercise

This query is meant to list every department that has no employee earning exactly 450000, but it returns an empty set because the subquery contains a NULL. Rewrite it using NOT EXISTS so it works correctly.

SELECT name
FROM departments
WHERE id NOT IN (SELECT department_id FROM employees WHERE salary = 450000)
;

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

Exercise

List the name of every employee who works in a department that has at least one active project. Use EXISTS with a correlated subquery against the projects table.

SELECT e.name
FROM employees e
WHERE EXISTS -- complete the subquery
;

SELECT e.name FROM employees e WHERE EXISTS (SELECT 1 FROM projects p WHERE p.department_id = e.department_id AND p.status = 'active');

Exercise

Why is NOT EXISTS generally safer than NOT IN when the subquery might return NULL?

Recap

IN keeps rows whose value is found in the subquery result set; NOT IN keeps rows whose value is absent.
EXISTS is true when the subquery returns at least one row; NOT EXISTS is true when it returns none.
NOT IN with a subquery that contains NULL silently returns nothing — switch to NOT EXISTS for safety.
EXISTS is often more efficient because it can stop at the first match instead of building the whole result set.

Next you will write subqueries that reference the outer query row by row — correlated subqueries — which unlock per-group comparisons like 'who earns more than their own department average?'

IN, NOT IN, EXISTS and NOT EXISTS

IN and NOT IN — membership tests

EXISTS and NOT EXISTS — existence tests

Choosing between IN and EXISTS

Recap

Checkpoint quiz

Go deeper — technical resources