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);