SQL — All

What is ALL?

ALL compares a value to every value in a result set. The condition is true only if it's true for all values.

Basic Syntax

SELECT column_name
FROM table_name
WHERE column_name > ALL (subquery);

Examples

Find salaries higher than everyone in the sales department

SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary
  FROM employees
  WHERE department = 'Sales'
);

Find products more expensive than all products in a category

SELECT product_name, price
FROM products
WHERE price > ALL (
  SELECT price
  FROM products
  WHERE category = 'Basic'
);

NOT + ALL = ANY

-- These are equivalent:
WHERE x != ALL (subquery)
WHERE x NOT IN (subquery)

ALL with Comparisons

Operator	Meaning
> ALL	Greater than every value
< ALL	Less than every value
>= ALL	Greater than or equal to every value
<= ALL	Less than or equal to every value
= ALL	Equal to every value (only useful if subquery returns one value)
!= ALL	Not equal to any value (same as NOT IN)

ALL vs ANY

-- ALL: must be true for EVERY row
WHERE salary > ALL (SELECT salary FROM interns);

-- ANY: must be true for AT LEAST ONE row
WHERE salary > ANY (SELECT salary FROM interns);

Practical Example

-- Find students who scored higher than every student in Class A
SELECT s.name, s.score
FROM students s
WHERE s.score > ALL (
  SELECT score
  FROM students
  WHERE class = 'A'
);

Handling NULLs

If the subquery returns any NULL values, ALL comparisons may not behave as expected. Filter NULLs in the subquery:

WHERE salary > ALL (
  SELECT salary FROM employees WHERE salary IS NOT NULL
);

Performance Tip

Most databases optimize ALL the same as NOT IN or NOT EXISTS. For large subqueries, consider rewriting with NOT EXISTS for clarity:

-- Equivalent to > ALL
WHERE NOT EXISTS (
  SELECT 1 FROM employees e2
  WHERE e2.department = 'Sales'
  AND e2.salary >= e1.salary
);

Practice

1. Find all products priced higher than every product in the "Budget" category
2. Find employees who earn more than all interns
3. Rewrite an ALL query using NOT EXISTS