15.2.15.7 Correlated Subqueries — MySQL 8.4

15.2.15.7 Связанные подзапросы

Связанный подзапрос — это подзапрос, который содержит ссылку на таблицу, которая также появляется во внешнем запросе. Например:

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

Обратите внимание, что подзапрос содержит ссылку на столбец таблицы t1, хотя в предложении FROM подзапроса не упоминается таблица t1. Поэтому MySQL обращается к внешнему запросу и находит t1 в нём.

Предположим, что таблица t1 содержит строку, где column1 = 5 и column2 = 6; в то же время, таблица t2 содержит строку, где column1 = 5 и column2 = 7. Простое выражение ... WHERE column1 = ANY (SELECT column1 FROM t2) было бы равно TRUE, но в данном примере предложение WHERE в подзапросе равно FALSE (потому что (5,6) не равно (5,7)), поэтому выражение в целом равно FALSE.

Правило области действия: MySQL вычисляет от внутреннего к внешнему. Например:

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

В этом операторе, x.column2 должен быть столбцом в таблице t2, потому что SELECT column1 FROM t2 AS x ... переименовывает t2. Это не столбец в таблице t1, потому что SELECT column1 FROM t1 ... — это внешний запрос, находящийся дальше.

Оптимизатор может преобразовать связанный скалярный подзапрос в производную таблицу, когда флаг subquery_to_derived переменной optimizer_switch включен. Рассмотрим показанный здесь запрос:

SELECT * FROM t1
    WHERE ( SELECT a FROM t2
              WHERE t2.a=t1.a ) > 0;

Чтобы избежать многократного материализования одной и той же производной таблицы, мы можем вместо этого один раз материализовать производную таблицу, добавив группировку по столбцу объединения из таблицы, на которую ссылается внутренний запрос (t2.a), а затем внешнее объединение по поднятому предикату (t1.a = derived.a), чтобы выбрать правильную группу, которая соответствует внешней строке. (Если в подзапросе уже есть явная группировка, дополнительная группировка добавляется в конец списка группировок.) Таким образом, показанный ранее запрос может быть переписан следующим образом:

SELECT t1.* FROM t1
    LEFT OUTER JOIN
        (SELECT a, COUNT(*) AS ct FROM t2 GROUP BY a) AS derived
    ON  t1.a = derived.a
        AND
        REJECT_IF(
            (ct > 1),
            "ERROR 1242 (21000): Subquery returns more than 1 row"
            )
    WHERE derived.a > 0;

В переписанном запросе REJECT_IF() представляет собой внутреннюю функцию, которая проверяет заданное условие (здесь, сравнение ct > 1) и вызывает заданную ошибку (в данном случае), если условие истинно. Это отражает проверку кардинальности, которую оптимизатор выполняет в рамках оценки предложения JOIN или WHERE, до оценки любого поднятого предиката, что выполняется только в том случае, если подзапрос возвращает не более одной строки.

Этот тип преобразования может быть выполнен при соблюдении следующих условий:

Подзапрос может быть частью списка SELECT, условия WHERE или условия HAVING, но не может быть частью условия JOIN и не может содержать предложения LIMIT или OFFSET. Кроме того, подзапрос не может содержать никаких операций над множествами, таких как UNION.
Предложение WHERE может содержать один или несколько предикатов, объединённых с помощью операторов AND. Если предложение WHERE содержит предложение OR, то оно не может быть преобразовано. По крайней мере, один из предикатов предложения WHERE должен быть пригоден для преобразования, и ни один из них не должен отказываться от преобразования.
Для того чтобы быть пригодным для преобразования, предикат предложения WHERE должен быть предикатом равенства, в котором каждый операнд должен быть простой ссылкой на столбец. Никакие другие предикаты — включая другие предикаты сравнения — не подходят для преобразования. Предикат должен использовать оператор равенства = для сравнения; безопасный для NULL оператор <=> в этом контексте не поддерживается.
Предикат предложения WHERE, содержащий только внутренние ссылки, не пригоден для преобразования, так как он может быть оценён до группировки. Предикат предложения WHERE, содержащий только внешние ссылки, пригоден для преобразования, даже если он может быть поднят до блока внешнего запроса. Это становится возможным благодаря добавлению проверки кардинальности без группировки в производной таблице.
Для того, чтобы быть пригодным, предикат предложения WHERE должен иметь один операнд, содержащий только внутренние ссылки, и один операнд, содержащий только внешние ссылки. Если предикат не подходит по этому правилу, преобразование запроса отклоняется.
Связанный столбец может быть присутствовать только в предложении WHERE подзапроса (и не в списке SELECT, предложении JOIN или ORDER BY, списке GROUP BY или предложении HAVING). Также не может быть связанного столбца внутри производной таблицы в списке FROM подзапроса.
Связанный столбец не может быть включён в список аргументов агрегатной функции.
Связанный столбец должен быть разрешен в блоке запроса, непосредственно содержащем подзапрос, рассматриваемый для преобразования.
Связанный столбец не может быть присутствовать в вложенном скалярном подзапросе в предложении WHERE.
Подзапрос не может содержать оконные функции и не может содержать агрегатной функции, агрегирующей в блоке запроса, внешнем по отношению к подзапросу. Агрегатная функция COUNT(), если она содержится в элементе списка SELECT подзапроса, должна быть на верхнем уровне и не может быть частью выражения.

См. также Раздел 15.2.15.8, «Производные таблицы».