MySql Subqueries with IN , ANY , SOME

Posted by admin

May 6, 2008

Syntax:
operand comparison_operator ANY (subquery)
operand IN (subquery)
operand comparison_operator SOME (subquery)
The ANY keyword, which must follow a comparison operator, means “return TRUE if the comparison
is TRUE for ANY of the rows 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 is TRUE if table t2
contains (21,14,7) because there is a value 7 in t2 that is less than 10. The expression is
FALSE if table t2 contains (20,10), or if table t2 is empty. The expression is UNKNOWN if table
t2 contains (NULL,NULL,NULL).
The word IN is an alias for = ANY, so 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);
The word SOME is an alias for ANY, so 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 the preceding example shows why it might be useful. To
most people’s ears, the English phrase “a is not equal to any b” means “there is no b which is
equal to a,” but that isn’t what is meant by the SQL syntax. Using <> SOME instead helps
ensure that everyone understands the true meaning of the query.