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 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 table t1
containing (10)
. The expression is
TRUE
if table t2
contains
(-5,0,+5)
because 10
is
greater than all three values in t2
. The
expression is FALSE
if table
t2
contains
(12,6,NULL,-100)
because there is a single
value 12
in table t2
that
is greater than 10
. The expression is
unknown (that is, NULL
)
if table t2
contains
(0,NULL,1)
.
Finally, the expression is TRUE
if table
t2
is empty. So, the following expression is
TRUE
when table t2
is
empty:
SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
But this expression is NULL
when table
t2
is empty:
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);
In addition, the following expression is NULL
when table t2
is empty:
SELECT * FROM t1 WHERE 1 > ALL (SELECT MAX(s1) FROM t2);
In general, tables containing NULL
values and empty 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 with IN
, ANY
, and
SOME
, you can use
TABLE
with ALL
and NOT IN
provided that the following two
conditions are met:
The table in the subquery contains only one column
The subquery does not depend on a column expression
For example, assuming that table t2
consists
of a single column, the last two statements shown previously can
be written using TABLE 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 as SELECT * FROM t1 WHERE 1 > ALL
(SELECT MAX(s1) FROM t2);
cannot be written using
TABLE t2
because the subquery depends on a
column expression.