Skip to content

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.

operand comparison_operator ALL (subquery)

Examples are as follows:

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:
SELECT * FROM t1 WHERE 1 > ALL (SELECT s1 FROM t2);
  • In the following example, when table t2 is empty, this expression is NULL:
SELECT * FROM t1 WHERE 1 > (SELECT s1 FROM t2);

or:

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

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

Example

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