(MySQL) ANY, ALL
Introduction
Let me explain them using an example.
Settings
|table n1|
| id | num |
| 1 | 82 |
| 2 | 47 |
| 3 | 62 |
| 4 | 16 |
|table n2|
| id | num |
| 1 | 71 |
| 2 | 56 |
| 3 | 93 |
| 4 | 36 |
ANY
SELECT * FROM n1 WHERE num > ANY (SELECT num FROM n2 WHERE id = 1 OR id = 2);
| id | num |
| 1 | 82 |
| 3 | 62 |
The subquery '(SELECT num FROM n2 WHERE id = 1 OR id = 2)' returns '(71, 56)'.
If 'ANY' is used, a WHERE clause becomes true when any of the subquery values satisfy the WHERE clause.
In the above example, {id: 3, num: 62} is selected because the num is larger than 56, even though it is lower than 71.
ALL
SELECT * FROM n1 WHERE num > ALL (SELECT num FROM n2 WHERE id = 1 OR id = 2);
| id | num |
| 1 | 82 |
Same as above, the subquery '(SELECT num FROM n2 WHERE id = 1 OR id = 2)' returns '(71, 56)'.
If 'ALL' is used, a WHERE clause becomes true only if all the subquery values satisfy the WHERE clause.
In the above example, {id: 3, num: 62} is not selected because the num is lower than 71, even though it is larger than 56.