IN, ANY, ALL, and EXISTS

x IN (...) equals x = ANY (...); x > ANY (or SOME) holds for at least one row and x > ALL for every row; <> ALL is NOT IN. EXISTS only asks whether a row comes back, so its select list is ignored. price > ALL (SELECT price FROM products WHERE category_id = 4) finds the four books dearer than every Design title. The edges hold the surprises:

ALL over an empty set, and IN meeting NULLSQL

SELECT (SELECT COUNT(*) FROM products WHERE price > ALL
          (SELECT price FROM products WHERE category_id = 9)) AS all_empty,
       (SELECT COUNT(*) FROM products WHERE price >
          (SELECT MAX(price) FROM products WHERE category_id = 9)) AS max_empty,
       3 IN (1, NULL) AS in_n, 3 NOT IN (1, NULL) AS not_in_n, EXISTS (SELECT NULL) AS ex;

Output

+-----------+-----------+------+----------+------+
| all_empty | max_empty | in_n | not_in_n | ex   |
+-----------+-----------+------+----------+------+
|         8 |         0 | NULL |     NULL |    1 |
+-----------+-----------+------+----------+------+
1 row in set (0.001 sec)

Category 9 does not exist: ALL over no rows is true for all 8 products, while MAX of nothing is NULL. The NULLs explain the NOT IN trap of Self and Multi-Table Joins: 3 might be the NULL, so IN and NOT IN are both unknown; EXISTS is never unknown. Write "no matching row" as NOT EXISTS, or filter IS NOT NULL inside the NOT IN. MySQL 524 rewrites IN and EXISTS into semijoins and, since 8.0.17, their negations into antijoins (Optimizer and EXPLAIN).

MENU

IN, ANY, ALL, and EXISTS