Subqueries With ANY and SOME

Compare a value against any result from a subquery. The ANY (or SOME) operator returns TRUE if the comparison holds for at least one row.

Syntax

SOME is a synonym for ANY.

scalar_expression comparison_operator ANY <Table subquery>
scalar_expression comparison_operator SOME <Table subquery>

scalar_expression may be any expression that evaluates to a single value.
comparison_operator may be any one of =, >, <, >=, <=, <> or !=.

About IN

Although IN functions similarly to = ANY, it is documented separately due to unique optimization characteristics. Refer to Subqueries with IN and NOT IN for more details.

ANY or SOME returns:

TRUE if the comparison operator returns TRUE for at least one row returned by the table subquery.
FALSE if the comparison operator returns FALSE for all rows returned by the table subquery, or if the table subquery has no rows.
NULL if the comparison operator returns NULL for at least one row returned by the table subquery and doesn't returns TRUE for any of them, or if scalar_expression returns NULL.

Examples

The subsequent examples use these tables:

CREATE TABLE sq1 (num1 TINYINT);
CREATE TABLE sq2 (num2 TINYINT);
INSERT INTO sq1 VALUES(100);
INSERT INTO sq2 VALUES(40),(50),(120);

Subquery With `ANY`

SELECT * FROM sq1 WHERE num1 > ANY (SELECT * FROM sq2);
+------+
| num1 |
+------+
|  100 |
+------+

100 is greater than two of the three values, and so the expression evaluates to true.

Subquery With `SOME`