Movatterモバイル変換

Syntax:

operandcomparison_operator ALL (subquery)

The wordALL, which must follow a comparison operator, means“returnTRUE if the comparison isTRUE forALL of the values in the column that the subquery returns.” For example:

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

Suppose that there is a row in tablet1 containing(10). The expression isTRUE if tablet2 contains(-5,0,+5) because10 is greater than all three values int2. The expression isFALSE if tablet2 contains(12,6,NULL,-100) because there is a single value12 in tablet2 that is greater than10. The expression isunknown (that is,NULL) if tablet2 contains(0,NULL,1).

Finally, the expression isTRUE if tablet2 is empty. So, the following expression isTRUE when tablet2 is empty:

SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);

But this expression isNULL when tablet2 is empty:

SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

In addition, the following expression isNULL when tablet2 is empty:

SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);

In general,tables containingNULL values andempty tables are“edge cases.” When writing subqueries, always consider whether you have taken those two possibilities into account.

NOT IN is an alias for<> ALL. Thus, these two statements are the same:

SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2);SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

As withIN,ANY, andSOME, you can useTABLE withALL andNOT IN provided that the following two conditions are met:

For example, assuming that tablet2 consists of a single column, the last two statements shown previously can be written usingTABLE t2 like this:

SELECT s1 FROM t1 WHERE s1 <> ALL (TABLE t2);SELECT s1 FROM t1 WHERE s1 NOT IN (TABLE t2);

A query such asSELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2); cannot be written usingTABLE t2 because the subquery depends on a column expression.