SQL · Pattern 8 — Subqueries
NOT EXISTS
The NOT EXISTS operator returns TRUE only when the subquery returns no rows.
Source data — Pattern 8: Subqueries
All queries run against the shared employees and departments tables (see schema.sql). This pattern introduces scalar, correlated, and multiple-row subqueries. Topics 47 and 48 demonstrate the EXISTS and NOT EXISTS operators, while Topics 49 and 50 introduce the ANY (SOME) and ALL comparison operators.
What it does
The NOT EXISTS operator returns TRUE only when the subquery returns no rows.
Problem
Find departments without employees.
SELECT department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 1
FROM employees e
WHERE e.department_id = d.department_id
);
Result
