Subqueries with ALL

Syntax:

<operand> <comparison operator> ALL (<subquery>)

The word ALL, which must follow a comparison operator, means return TRUE if the comparison is TRUE for ALL of the rows that the subquery returns. For example,

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

Suppose that there is a row in table t1 containing {10}. The expression is TRUE if table t2 contains {-5,0,+5} because all three values in t2 are less than 10. The expression is FALSE if table t2 contains {12,6,NULL,-100} because there is a single value in table t2 -- 12 -- which is greater than 10. The expression is UNKNOWN if table t2 contains {0,NULL,1}.

Finally, if table t2 is empty, the result is TRUE. You might think the result should be UNKNOWN, but sorry, it's TRUE. So, rather oddly,

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

is TRUE when table t2 is empty, but

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

is UNKNOWN when table t2 is empty. In addition,

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

is UNKNOWN when table t2 is empty. In general, tables with NULLs and empty tables are edge cases -- when writing subquery code, always consider whether you have taken those two possibilities into account.

Retornar