ALL Operator
SQL · SQL Subqueries
Theory
ALL compares a value with every value returned by a subquery, and the condition is TRUE only if it holds for all of them. salary > ALL (SELECT salary FROM interns) keeps employees who earn more than every intern, which in practice means more than the highest intern salary. It works with any comparison operator: =, <>, <, >, <=, >=.
Two edge cases matter. If the subquery returns no rows, ALL is TRUE for every outer row, which can surprise you. If the subquery contains a NULL and no value fails the test, the result is UNKNOWN and the row is dropped. Because of this, many people write the clearer equivalent salary > (SELECT MAX(salary) FROM interns), which is also what you must use on engines without ALL, such as SQLite. Note that <> ALL means the same as NOT IN.
Why we use it
Some rules must beat or meet every value in a set: higher than all competitor prices, earlier than all deadlines. ALL states "for every value" directly in the comparison.
When to use it
Use ALL when the condition must hold against every value of a subquery. Often > MAX() or < MIN() is easier to read and handles empty sets more predictably. For "at least one" logic, use ANY.
How it works
- Take an outer row
- Run the subquery to get the list of values
- Is the list empty? Yes: ALL is TRUE: keep the row. No: Compare against each value.
- Does the comparison hold for every value? Yes: Keep the row. No: Discard the row.
- Move to the next row
Syntax
SELECT columns FROM table
WHERE column > ALL (subquery);Example
SELECT name FROM employees
WHERE salary > ALL (SELECT salary FROM interns);Key points
- Often used with >, <, >=, <=
- True only if the comparison holds against every returned row
- > ALL works like > MAX(); < ALL works like < MIN()
- An empty subquery makes ALL TRUE for every row
- SQLite lacks ALL: rewrite with MAX() or MIN()