Movatterモバイル変換

Syntax:

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

Wherecomparison_operator is one of these operators:

=  >  <  >=  <=  <>  !=

TheANY keyword, which must follow a comparison operator, means“returnTRUE if the comparison isTRUE 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 tablet1 containing(10). The expression isTRUE if tablet2 contains(21,14,7) because there is a value7 int2 that is less than10. The expression isFALSE if tablet2 contains(20,10), or if tablet2 is empty. The expression isunknown (that is,NULL) if tablet2 contains(NULL,NULL,NULL).

When used with a subquery, the wordIN 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 wordSOME 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 wordSOME 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.

You can useTABLE in a scalarIN,ANY, orSOME subquery provided the table contains only a single column. Ift2 has only one column, the statements shown previously in this section can be written as shown here, in each case substitutingTABLE t2 forSELECT 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);