# **Subqueries with ALL**

## **Grammar Description**

The keyword `ALL` must be followed by the comparison operator, which means that if the comparison of `ALL` of the column value returned by the subquery is `TRUE`, then `TRUE` is returned.

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

Examples are as follows:

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

As in the above example, assuming that a row in Table t1 contains (10) and Table t2 contains (-5,0, +5), the expression is `TRUE`, because 10 is greater than all three values in t2. If Table t2 contains (12,6,NULL, -100), the expression is `FALSE` because there is a value greater than 10 in Table t2 12. If table t2 contains (0,NULL,1), the expression is `NULL`.

- If Table t2 is empty, the expression is `TRUE`. For example, when the following table t2 is empty, the expression is `TRUE`:

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

- In the following example, when table t2 is empty, this expression is `NULL`:

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

or:

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

**Note**: When writing subquery syntax, pay attention to the situation of tables containing `NULL` values and empty tables.

## **Grammar Structure**

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

## **Example**

```text
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
```