Skip to content
Tutorials Center
My learning

Topics

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.

SQL
-- PostgreSQL, MySQL, SQL Server, Oracle (not SQLite)
SELECT title, price
FROM books
WHERE price > ALL (SELECT price FROM books WHERE genre = 'Poetry');

Try it

SQL ALL replaced by MAX (SQLite) ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL ANY replaced by MIN ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

SQL ANY is not available in SQLite (fails on purpose) ยท database: bookstore

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

SQL
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).
SQL
-- PostgreSQL: match any element of an array parameter
SELECT * FROM books WHERE genre = ANY (ARRAY['Fiction', 'Poetry']);

Usage notes

  • = ANY is IN. Most people write IN; ANY matters for the other operators.
  • <> ANY is not NOT 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 NULL sets. ALL over an empty set is true; ANY over an empty set is false. A NULL in the set can make ALL unknown, so rows are dropped, as with NOT IN. The MAX()-based rewrite returns unknown for an empty set instead of true, so the two forms differ on empty subqueries.
  • Readability. A comparison with MIN() or MAX() is usually clearer and works in every database.
  • Performance. Optimizers can rewrite > ANY and > ALL to use min and max, but an aggregate rewrite is always safe.