13.2.10.3 Subqueries with ANY, IN, or SOME

operandcomparison_operator ANY (subquery)
operand IN (subquery)
operandcomparison_operator SOME (subquery)

Where comparison_operator is one of
these operators:

= > < >= <= <> !=

The ANY keyword, which must follow a
comparison operator, means “return TRUE
if the comparison is TRUE for
ANY 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 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 (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);

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 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.

User Comments

SELECT s1 FROM t1 WHERE s1 <> SOME (SELECT s1 FROM t2); example:if there is a row containing (10) in table t1, the expression is true if table t2 contains (20,10) because 10 is not equal to one or more (i.e., some) of t2's values, namely the 20.

A NOT IN condition, otoh, would be false. NOT IN is always going to be at least as hard to satisfy (as <> SOME) because to be true /all/ rows have to be non-equal.

#find all paperid that are not dont from reviewing# Finish reviewing paper are paper that# for each reviewing answer 9 QAs# for paper there are three done reviewings.# We want to find paper that are not done from reviewing.SELECT paperid from paperWHERE paperid not in( SELECT paperid FROM ( SELECT paperid, COUNT(paperid) as numdonereviewing from ( SELECT reviewing.paperid , COUNT(*) as numcheck from reviewing, score where reviewing.reviewingid = score.reviewingid GROUP by reviewing.reviewingid ) as temp1 WHERE numcheck = 9 GROUP by paperid ) AS temp2 WHERE numdonereviewing >= 3);