SQL: подзапросы, их виды, корректное использование

Навигация по уроку

Подзапросы, возвращающие единственное значение
Подзапросы, возвращающие множество значений
Некоррелирующие и коррелирующие подзапросы

Связанные темы

Подзапросы, возвращающие единственное значение

Подзапросы (вложенные запросы), возвращающие единственное значение, наиболее часто применяются в случаях, когда значение определённого столбца в основном запросе требуется сравнить с некоторым единственным значением при помощи одного из операторов сравнения (=, <, >, <=, >=). Значение, с которым производится сравнение, как раз и возвращается подзапросом (вложенным запросом).

Верные признаки того, что подзапрос вернёт одно единственное значение:

в подзапросе применяется одна из агрегатных функций (COUNT, SUM, AVG, MAX, MIN);
подзапрос извлекает значение уникального идентификатора, например, первичного ключа.

В остальных случаях нужно быть полностью уверенным, что условиям, указанным в секции WHERE подзапроса, соответствует единственное значение выбираемого столбца.

В примерах работаем с базой данных "Театр". Таблица Play содержит данные о постановках. Таблица Team - о ролях актёров. Таблица Actor - об актёрах. Таблица Director - о режиссёрах. Поля таблиц, первичные и внешние ключи можно увидеть на рисунке ниже (для увеличения нажать левой кнопкой мыши).

Пример 1. Вывести спектакли режиссёра John Barton. Запрос будет следующим:

SELECT Name FROM PLAY WHERE Dir_ID=(SELECT Dir_ID FROM DIRECTOR WHERE FName='John' AND LName='Barton')

Поскольку подзапрос возвращает значение уникального идентификатора, можно быть уверенным в том, что он вернёт единственное значение.

Более редкий случай использования подзапросов, возвращающих единственное значение - в запросах с предикатом BETWEEN, где подзапросы задают границы интервала.

Подзапросы, возвращающие множество значений

Подзапросы, возвращающие множество значений, могут применяться в запросах с предикатами IN и EXISTS и кванторными функциями ALL и ANY.

Пример 2. В таблице TEAM столбец Mainteam содержит данные о том, главная ли роль закреплена с спектакле за актёром. Значение столбца 'Y' означает "да", 'N' - "нет". Вывести список актёров, которые когда-либо исполняли главные роли. Запрос будет следующим:

SELECT FName, LName FROM ACTOR WHERE Actor_ID IN (SELECT Actor_ID FROM TEAM WHERE Mainteam='Y')

Подзапрос вернёт множество значений FName и LName, которые через ключ Actor_ID будет передано в основной запрос и окончательно выведены в качестве результата.

На сайте есть отдельный урок о запросах с предикатом IN.

Пример 3. В таблице ACTOR (актёр) есть столбец SEX, содержащий данные о поле ('M' - мужской, 'F' - женский) актёра. Вывести спектакли, в которых играют только мужчины. Запрос будет следующим:

SELECT pl.* FROM PLAY pl WHERE NOT EXISTS (SELECT 1 FROM TEAM te JOIN ACTOR ac ON ac.Actor_ID=te.Actor_ID WHERE te.Play_ID=pl.Play_ID AND ac.Sex='F')

Предикат NOT EXISTS принимает подзапрос как аргумент и оценивает его как подходящий, если значения sex для одного или более актёров в таблице actor не равны F.

На сайте есть подробный урок о запросах с предикатами EXISTS и NOT EXISTS.

Пример 4. Определить самый популярный жанр театра. Пишем запрос с использованием кванторной функции ALL:

SELECT Genre FROM PLAY GROUP BY Genre HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM PLAY GROUP BY Genre)

Кванторная функция ALL проверяет значения количества жанров среди всех спектаклей и затем находит жанр с количеством большим, чем у любого другого жанра, или равным ему.

На сайте есть подробный урок о запросах с кванторными функциями ALL и ANY.

Некоррелирующие и коррелирующие подзапросы

Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца не должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется некоррелирующим. Результат выполнения некоррелирующего запроса не зависит от значений, возвращаемых основным запросом. Обычно некоррелирующие запросы применяются в запросах, в которых значение определённого столбца сравнивается со значением, возвращаемым подзапросом, в запросах с предикатом IN, кванторными функциями ALL и ANY. Однако уже в запросах с предикатом EXISTS применяются коррелирующие подзапросы.

Подзапрос, возвращающий результат или результаты, для получения которых значения указанного столбца должны соотноситься (коррелировать) со значениями столбцов, указанных в основном запросе, называется коррелирующим. Иными словами, результат, выполнения подзапроса зависит от значений, возвращаемых основным запросом. Часто коррелирующие подзапросы применяются для получения значений одного из столбцов результирующей таблицы и в этих случаях подзапрос, заключённый в скобки, перечисляется через запятую вместе с именами столбцов из таблиц или соединения таблиц.

Пример 5. Вывести список актёров с количеством их ролей. Пишем следующий запрос с коррелирующим подзапросом:

SELECT DISTINCT a.Actor_ID, a.FName, a.LName, (SELECT COUNT(*) FROM ACTOR a1 JOIN team t1 ON a1.Actor_ID=t1.ACTOR_ID WHERE a1.Actor_ID=a.Actor_ID GROUP BY a1.Actor_ID) AS NumRoles FROM ACTOR a JOIN team t ON a.Actor_ID=t.ACTOR_ID ORDER BY a.Actor_ID

В основном запросе происходит первое обращение к таблице ACTOR, которая получает псевдоним a. В подзапросе происходит второе обращение к таблице ACTOR, которая получает псевдоним a1. При этом в секции WHERE подзапроса указано условие: идентификаторы актёров, возвращаемые основным запросом и подзапросом, должны совпадать. Это условие - характерный признак коррелирующего подзапроса.

Поделиться с друзьями

Назад<<<

Содержание

Вперёд>>>