10.2.2.6 Optimizing ANY and ALL Subqueries

MySQL 9.3 supports transformation of general quantified comparison predicates to semijoins or antijoins. This includes all =, <>, >, >=, <, <= comparisons with ANY or ALL. Transformations to derived tables of such comparisons are supported in both the SELECT clause and in the WHERE clause of the outer query.

(A quantified comparison operator such as ANY, ALL, or SOME compares a value to a set of values. = ANY is equivalent to IN; <> ALL is equivalent to NOT IN. See Section 15.2.15.3, “Subqueries with ANY, IN, or SOME”, and Section 15.2.15.4, “Subqueries with ALL”, for more information.)

A subquery which qualifies for the transformation is converted to a derived table as follows:

For =ANY and <>ALL predicates:
1. The subquery is converted into a derived table.
2. The selected expression from the subquery is converted from expr to MIN(expr) or MAX(expr), depending on the actual predicate.
3. Select COUNT(*) from the subquery.
4. Select the number of distinct non-null values.
For other predicates:
1. The subquery is converted into a derived table.
2. The selected expression from the subquery is selected from the derived table. (There can be more than one such expression.)
3. Unless the selected expression is distinct, duplicate elimination is performed.
4. If the selected expression is nullable, and processing of nulls is necessary, select an indication of whether the subquery contains any nulls.

Note

When the semijoin optimizer switch is enabled, that switch takes precedence over subquery_to_derived for those queries where either transform is possible.

The outer query block must contain at least one table, and may not contain more than 60 tables.

The quantified comparison predicate must be placed in the WHERE clause or the SELECT list of the outer query; it cannot be in a JOIN clause, a GROUP BY clause, a HAVING clause, an ORDER BY clause, a QUANTIFY clause, or a window specification's ORDER BY or PARTITION BY clause.

A <>ALL or =ANY predicate, when placed in the SELECT list of the outer query, must not contain any nullable expressions in the left-hand expression or in the selected expression or expressions of the subquery.

A <>ALL predicate, when placed in the WHERE clause of the outer query, must not contain any nullable expressions in the left-hand expression or in the selected expression or expressions of the subquery.

The query expression for the subquery cannot contain any of the following:

A set operation (any of UNION, INTERSECT, or EXCEPT).
An explicit grouping, except when the resolver is able to eliminate grouping.
Any implicit grouping.
A WINDOW clause.

The inner query block must also reference at least one table, and may contain outer references inside an equality clause in the supported condition. It must be the single predicate of the condition, or be combined with other predicates and conditions with enclosing AND operators.

Results are calculated as follows:

=ANY (IN):
- If the derived table is empty, the result is FALSE.
- If the value sought is found in the derived table, the result is TRUE.
- If the value sought is NULL, or the derived table contains a NULL, the result is UNKNOWN; otherwise, the result is FALSE.
- If the result is UNKNOWN and the predicate ignores UNKNOWN, the result is FALSE.
Example. The following query
```
SELECT * FROM ot WHERE ot.a = ANY (SELECT it.b FROM it);
```
is transformed to this:
```
SELECT ot.*
  FROM ot 
  INNER JOIN (SELECT DISTINCT it.b AS dt_b) FROM it AS dt 
  ON ot.a = dt.dt_b;
```
<>ALL (NOT IN):
- If the derived table is empty, the result is TRUE.
- If the value sought is found in the derived table, the result is FALSE.
- If the value sought is NULL, or the derived table contains a NULL, the result is UNKNOWN; otherwise, the result is TRUE.
- If the result is UNKNOWN and the predicate ignores UNKNOWN, the result is FALSE.
Example. The following query
```
SELECT * FROM ot WHERE ot.a <>ALL (SELECT it.b FROM it);
```
is transformed into the equivalent query
```
SELECT ot.*
  FROM ot 
  OUTER JOIN (SELECT DISTINCT it.b AS dt_b) FROM it AS dt 
  ON ot.a = dt.dt_b;
```
<>ANY:
- If the derived table is empty, the result is FALSE.
  If the value sought is NULL, the result is UNKNOWN.
  If there is more than one distinct, non-null value in the derived table, the result is TRUE.
  If any value in the derived table is equal to the value sought, the result is FALSE, otherwise TRUE.
  If the result is UNKNOWN and the predicate ignores UNKNOWN, the result is FALSE.
=ALL:
- If the derived table is empty, the result is TRUE.
- If the value sought is NULL, the result is UNKNOWN.
- If there is more than one distinct, non-null value in the derived table, or the (single) distinct value is different from the value sought, the result is FALSE.
- If the subquery contains at least one NULL, the result is UNKNOWN, otherwise FALSE.
- If the result is UNKNOWN and the predicate ignores UNKNOWN, the result is FALSE.
>ANY, >=ANY, <ANY, <=ANY:
- If the derived table is empty, the result is FALSE.
- If the value sought is NULL, the result is UNKNOWN.
- Calculate the result of the >, >=, <, or <= operator between the left-hand expression and the MIN() or MAX() of the expression from the subquery (MIN() for > and >= comparisons, MAX() for < and <=). If this is true, the result is TRUE. Otherwise, if the derived table contains at least one NULL, the result is UNKNOWN.
- If the result is UNKNOWN and the predicate ignores UNKNOWN, the result is FALSE.
Example. The query shown here
```
SELECT * FROM ot WHERE ot.a > ANY (SELECT it.b FROM it);
```
```
SELECT ot.*
  FROM ot JOIN (SELECT MIN(it.b) AS min_b) FROM it AS dt 
  ON ot.a > dt.min_b;
```
>ALL, >=ALL, <ALL, <=ALL:
- If the derived table is empty, the result is TRUE.
- If the value sought is NULL, or the derived table contains at least one NULL, the result is UNKNOWN; otherwise, calculate the result of the >, >=, <, or <= operator between the left-hand expression and the MIN() or MAX() of the expression from the subquery (TRUE or FALSE).
- If the result is UNKNOWN and the predicate ignores UNKNOWN, the result is FALSE.
Example. The following query
```
SELECT * FROM ot WHERE ot.a > ALL (SELECT it.b FROM it)
```
is transformed into the query shown here:
```
SELECT ot.*
  FROM ot 
  OUTER JOIN (SELECT MAX(it.b) AS max_b,
                     COUNT(*) AS c,
                     SUM(it.b IS NULL AS nulls)
              FROM it AS dt) 
  ON TRUE
WHERE c = 0 OR (ot.a > max_b AND nulls = 0);
```

Note

The transformation cannot be applied to a query having a SELECT list with a quantified comparison predicate when it also employs a window function referencing the same predicate.