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

URL: https://mariadb.com/kb/en/subqueries-and-all/