Коррелированный подзапрос - Correlated subquery
В SQL база данных запрос, а коррелированный подзапрос (также известный как синхронизированный подзапрос) - это подзапрос (запрос, вложенный в другой запрос), который использует значения из внешнего запроса. Поскольку подзапрос может оцениваться один раз для каждой строки, обрабатываемой внешним запросом, это может быть медленным.
Вот пример типичного коррелированного подзапроса. В этом примере цель состоит в том, чтобы найти всех сотрудников, чья зарплата выше средней по их отделу.
ВЫБРАТЬ количество сотрудников, имя ИЗ сотрудники emp КУДА зарплата > ( ВЫБРАТЬ AVG(зарплата) ИЗ сотрудники КУДА отделение = emp.отделение);В приведенном выше запросе внешний запрос
ВЫБРАТЬ количество сотрудников, имя ИЗ сотрудники emp КУДА зарплата > ...а внутренний запрос (коррелированный подзапрос)
ВЫБРАТЬ AVG(зарплата) ИЗ сотрудники КУДА отделение = emp.отделениеВ приведенном выше вложенном запросе внутренний запрос должен быть повторно выполнен для каждого сотрудника. (Достаточно продуманная реализация может кэшировать результат внутреннего запроса для каждого отдела, но даже в лучшем случае внутренний запрос должен выполняться один раз для каждого отдела. См. «Оптимизация коррелированных подзапросов» ниже.)
Связанные подзапросы могут появляться где угодно, кроме Предложение WHERE; например, в этом запросе используется коррелированный подзапрос в Предложение SELECT чтобы распечатать весь список сотрудников вместе со средней зарплатой по каждому отделу сотрудника. Опять же, поскольку подзапрос коррелирован со столбцом внешнего запроса, он должен быть повторно выполнен для каждой строки результата.[нужна цитата ]
ВЫБРАТЬ количество сотрудников, имя, (ВЫБРАТЬ AVG(зарплата) ИЗ сотрудники КУДА отделение = emp.отделение) В КАЧЕСТВЕ Department_average ИЗ сотрудники empОбычно бессмысленно иметь коррелированный подзапрос в предложении FROM, потому что таблица в предложении FROM необходима для оценки внешнего запроса, но коррелированный подзапрос в предложении FROM не может быть оценен до оценки внешнего запроса, что вызывает проблема курицы и яйца. Конкретно, MariaDB перечисляет это как ограничение в своей документации.[1]
Однако в некоторых системах баз данных разрешено использовать коррелированные подзапросы при объединении в предложении FROM, ссылаясь на таблицы, перечисленные перед объединением, с использованием указанного ключевого слова, создавая ряд строк в коррелированном подзапросе и присоединяя его к таблице на оставили. Например, в PostgreSQL, добавив ключевое слово LATERAL перед правым подзапросом,[2] или в SQL Server, используя ключевое слово CROSS APPLY или OUTER APPLY вместо JOIN[3] достигает эффекта.
Эффект коррелированных подзапросов в некоторых случаях может быть получен с помощью присоединяется. Например, приведенные выше запросы (в которых используются неэффективные коррелированные подзапросы) можно переписать следующим образом.
- Этот подзапрос не коррелирует с внешним запросом и поэтому - выполняется только один раз, независимо от количества сотрудников. ВЫБРАТЬ сотрудники.количество сотрудников, сотрудники.имя ИЗ сотрудники ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ (ВЫБРАТЬ отделение, AVG(зарплата) В КАЧЕСТВЕ Department_average ИЗ сотрудники ГРУППА К отделение) темп НА сотрудники.отделение = темп.отделение КУДА сотрудники.зарплата > темп.Department_average;Если внутренний запрос используется в нескольких запросах, внутренний запрос можно сохранить как представление, а затем присоединиться к представлению:
СОЗДАЙТЕ ПОСМОТРЕТЬ dept_avg В КАЧЕСТВЕ ВЫБРАТЬ отделение, AVG(зарплата) В КАЧЕСТВЕ Department_average ИЗ сотрудники ГРУППА К отделение; - Перечислите сотрудников, зарабатывающих больше, чем в среднем по отделу. ВЫБРАТЬ сотрудники.количество сотрудников, сотрудники.имя ИЗ сотрудники ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ dept_avg НА сотрудники.отделение = dept_avg.отделение КУДА сотрудники.зарплата > dept_avg.Department_average; - Составьте список сотрудников вместе со средними показателями по соответствующему отделу. ВЫБРАТЬ сотрудники.количество сотрудников, сотрудники.имя, dept_avg.Department_average ИЗ сотрудники ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ dept_avg НА сотрудники.отделение = dept_avg.отделение; УРОНИТЬ ПОСМОТРЕТЬ dept_avg;Вы также можете создать временную таблицу и ссылаться на нее вместо представления.
Другой способ добиться этого, который будет иметь ту же производительность, что и решение «просмотра», - использовать CTE (общее табличное выражение) следующим образом. Это имеет то преимущество, что вся операция выполняется в одном запросе, если это необходимо. Обратите внимание, что некоторые версии SQL, обычно более старые, не поддерживают операцию «With ... CTE».
С ВЫБРАТЬ отделение, AVG(зарплата) В КАЧЕСТВЕ Department_average ИЗ сотрудники ГРУППА К отделениеВ КАЧЕСТВЕ dept_avg_CTE - произвольное имя, не требует "CTE" - Перечислите сотрудников, зарабатывающих больше, чем в среднем по отделу. ВЫБРАТЬ сотрудники.количество сотрудников, сотрудники.имя ИЗ сотрудники ВНУТРЕННИЙ ПРИСОЕДИНИТЬСЯ dept_avg_CTE НА сотрудники.отделение = dept_avg_CTE.отделение КУДА сотрудники.зарплата > dept_avg_CTE.Department_average;Реализации баз данных, такие как Oracle, могут автоматически отключать коррелированный подзапрос, если оптимизатор, основанный на стоимости, считает, что это дает лучший план выполнения.