ALL

The ALL operator compares a value with every value returned by the subquery.

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.

ALL — source table 1

ALL — source table 2

What it does

The ALL operator compares a value with every value returned by the subquery. The condition is true only if it satisfies the comparison for all returned values.

Problem

Find employees earning more than all HR employees.

SELECT first_name, salary
FROM employees
WHERE salary > ALL (
SELECT salary
FROM employees
WHERE department_id = 200
);

Result

ALL — result 1

SQL · Pattern 8 — Subqueries

ALL