# Subqueries with ALL

## Description

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.":

``````operand comparison_operator ALL (subquery)
``````

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, and the result returns `Empty set`. 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.

## Syntax

``````> SELECT column_name(s) FROM table_name {WHERE | HAVING} [not] expression comparison_operator ALL (subquery)
``````

## Examples

``````create table t1 (a int);
create table t2 (a int, b int);
create table t3 (a int);
create table t4 (a int not null, b int not null);
create table t5 (a int);
create table t6 (a int, b int);
insert into t1 values (2);
insert into t2 values (1,7),(2,7);
insert into t4 values (4,8),(3,8),(5,9);
insert into t5 values (null);
insert into t3 values (6),(7),(3);
insert into t6 values (10,7),(null,7);

mysql> select * from t3 where a <> all (select b from t2);
+------+
| a    |
+------+
|    6 |
|    3 |
+------+
2 rows in set (0.00 sec)

mysql> select * from t4 where 5 > all (select a from t5);
+------+------+
| a    | b    |
+------+------+
|    4 |    8 |
|    3 |    8 |
|    5 |    9 |
+------+------+
3 rows in set (0.01 sec)

mysql> select * from t3 where 10 > all (select b from t2);
+------+
| a    |
+------+
|    6 |
|    7 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> select a,b from t6 where a > all ( select a ,b from t4 where a>3);
ERROR 1105 (HY000): subquery should return 1 column
``````