Subqueries with ANY, IN, and SOME

Syntax:

<operand> <comparison operator> ANY (<subquery>)
<operand> IN (<subquery>)
<operand> <comparison operator> SOME (<subquery>)

The word ANY, 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 in t2 -- 7 -- which 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. 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);

The word SOME 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 <> SOME (SELECT s1 FROM t2);

Use of the word SOME is rare, but the above example shows why it might be useful. The English phrase a is not equal to any b means, to most people's ears, there is no b which is equal to a -- which isn't what is meant by the SQL syntax. By using <> SOME instead, you ensure that everyone understands the true meaning of the query.

Retornar