15.2.15.3 Subqueries with ANY, IN, or SOME (original) (raw)

15.2.15.3 Subqueries with ANY, IN, or SOME

Syntax:

operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)

Where comparisonoperator is one of these operators:

=  >  <  >=  <=  <>  !=

The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison is TRUE forANY of the values in the column that the subquery returns.” For example:

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

Suppose that there is a row in table t1 containing (10). The expression isTRUE if table t2 contains(21,14,7) because there is a value7 in t2 that is less than10. The expression isFALSE if table t2 contains(20,10), or if table t2 is empty. The expression is unknown (that is,NULL) if table t2 contains(NULL,NULL,NULL).

When used with a subquery, the word IN is an alias for = ANY. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 IN    (SELECT s1 FROM t2);

IN and = ANY are not synonyms when used with an expression list.IN can take an expression list, but= ANY cannot. SeeSection 14.4.2, “Comparison Functions and Operators”.

NOT IN is not an alias for <> ANY, but for <> ALL. SeeSection 15.2.15.4, “Subqueries with ALL”.

The word SOME is an alias forANY. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ANY  (SELECT s1 FROM t2);
SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2);

Use of the word SOME is rare, but this example shows why it might be useful. To most people, the English phrase “a is not equal to any b” means“there is no b which is equal to a,” but that is not what is meant by the SQL syntax. The syntax means“there is some b to which a is not equal.” Using<> SOME instead helps ensure that everyone understands the true meaning of the query.

Beginning with MySQL 8.0.19, you can useTABLE in a scalarIN, ANY, orSOME subquery provided the table contains only a single column. If t2 has only one column, the statements shown previously in this section can be written as shown here, in each case substituting TABLE t2 for SELECT s1 FROM t2:

SELECT s1 FROM t1 WHERE s1 > ANY (TABLE t2);

SELECT s1 FROM t1 WHERE s1 = ANY (TABLE t2);

SELECT s1 FROM t1 WHERE s1 IN (TABLE t2);

SELECT s1 FROM t1 WHERE s1 <> ANY  (TABLE t2);

SELECT s1 FROM t1 WHERE s1 <> SOME (TABLE t2);