# 13.2.10.4 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 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);``````