Subqueries and ALL

Subqueries using the ALL keyword will return true if the comparison returns true for each row returned by the subquery, or the subquery returns no rows.

Syntax

scalar_expression comparison_operator ALL <Table subquery>

• scalar_expression may be any expression that evaluates to a single value
• comparison_operator may be any one of: =, >, <, >=, <=, <> or !=

ALL returns:

• NULL if the comparison operator returns NULL for at least one row returned by the Table subquery or scalar_expression returns NULL.
• FALSE if the comparison operator returns FALSE for at least one row returned by the Table subquery.
• TRUE if the comparison operator returns TRUE for all rows returned by the Table subquery, or if Table subquery returns no rows.

NOT IN is an alias for <> ALL.

Examples

CREATE TABLE sq1 (num TINYINT);

CREATE TABLE sq2 (num2 TINYINT);

INSERT INTO sq1 VALUES(100);

INSERT INTO sq2 VALUES(40),(50),(60);

SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);
+------+
| num  |
+------+
|  100 |
+------+

Since 100 > all of 40,50 and 60, the evaluation is true and the row is returned

Adding a second row to sq1, where the evaluation for that record is false:

INSERT INTO sq1 VALUES(30);

SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);
+------+
| num  |
+------+
|  100 |
+------+

Adding a new row to sq2, causing all evaluations to be false:

INSERT INTO sq2 VALUES(120);

SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);
Empty set (0.00 sec)

When the subquery returns no results, the evaluation is still true:

SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2 WHERE num2 > 300);
+------+
| num  |
+------+
|  100 |
|   30 |
+------+

Evaluating against a NULL will cause the result to be unknown, or not true, and therefore return no rows:

INSERT INTO sq2 VALUES (NULL);

SELECT * FROM sq1 WHERE num > ALL (SELECT * FROM sq2);