15.2.15.7 Correlated Subqueries — MySQL 9.2

15.2.15.7 Коррелированные подзапросы

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

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

Обратите внимание, что подзапрос содержит ссылку на столбец таблицы t1, даже если в условии подзапроса не упоминается таблица 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 и не может содержать условия OFFSET. Подзапрос может содержать LIMIT 1, но не может содержать другие условия LIMIT; он должен использовать литерал 1 и не может использовать никакие другие значения, плейсхолдеры (?) или переменные. Кроме того, подзапрос не может содержать никаких операций над множествами, таких как 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, «Производные таблицы».