SQL Reference ยท Joins and Subqueries
SQL ANY, SOME and ALL
Reference for SQL ANY, SOME and ALL: compare a value with every row of a subquery, how they relate to IN, MIN and MAX, and SQLite equivalents.
ANY, SOME and ALL are quantified comparison operators. They compare a value with a whole set of values returned by a subquery. x > ANY (subquery) is true if x is greater than at least one value in the set. x > ALL (subquery) is true if x is greater than every value. SOME is a synonym for ANY.
SQLite does not support them. They exist in PostgreSQL, MySQL, SQL Server and Oracle. The equivalents are IN and comparisons with MIN() or MAX(), which the runnable examples below use.
-- PostgreSQL, MySQL, SQL Server, Oracle (not SQLite) SELECT title, price FROM books WHERE price > ALL (SELECT price FROM books WHERE genre = 'Poetry');
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
SQLite's message for the failing example is Error: near "SELECT": syntax error; it does not recognize ANY as an operator.
Syntax
expr operator ANY (subquery) expr operator SOME (subquery) expr operator ALL (subquery)
| Part | Description |
|---|---|
operator |
A comparison: =, <>, <, <=, >, >=. |
ANY / SOME |
True if the comparison holds for at least one value of the subquery. False if the subquery is empty. |
ALL |
True if the comparison holds for every value. True if the subquery is empty. |
| Expression | Equivalent |
|---|---|
x = ANY (s) |
x IN (s) |
x <> ALL (s) |
x NOT IN (s) |
x > ANY (s) |
x > (SELECT MIN(...)) |
x > ALL (s) |
x > (SELECT MAX(...)) |
x < ANY (s) |
x < (SELECT MAX(...)) |
x < ALL (s) |
x < (SELECT MIN(...)) |
Dialect differences
| Database | Support |
|---|---|
| SQLite | None. Use IN, MIN()/MAX() or EXISTS. |
| PostgreSQL | Subquery form, and an array form: x = ANY (ARRAY[1, 2, 3]). |
| MySQL | Subquery form only. |
| SQL Server | Subquery form. |
| Oracle | Subquery form, and lists: x > ANY (10, 20, 30). |
-- PostgreSQL: match any element of an array parameter SELECT * FROM books WHERE genre = ANY (ARRAY['Fiction', 'Poetry']);
Usage notes
= ANYisIN. Most people writeIN;ANYmatters for the other operators.<> ANYis notNOT IN. It means "different from at least one value", which is true for almost every row when the set has two or more distinct values. You usually want<> ALL.- Empty and
NULLsets.ALLover an empty set is true;ANYover an empty set is false. ANULLin the set can makeALLunknown, so rows are dropped, as with NOT IN. TheMAX()-based rewrite returns unknown for an empty set instead of true, so the two forms differ on empty subqueries. - Readability. A comparison with
MIN()orMAX()is usually clearer and works in every database. - Performance. Optimizers can rewrite
> ANYand> ALLto use min and max, but an aggregate rewrite is always safe.
Related
- IN with a subquery, EXISTS, scalar subqueries, MIN(), MAX()
- Lessons: Subqueries, Operators