Подзапросы
Знакомьтесь: подзапросы!
Подзапрос — это запрос в запросе. Например, ассистент по актёрам собирает портфолио с фото и пробами артистов, подходящих на роль Гамлета в новом фильме. А затем режиссёр, изучив портфолио, приглашает на кастинг пятерых, с которыми готов работать. Деятельность ассистента по актёрам — это подзапрос, или внутренний запрос. А выбор лучшего Гамлета из пяти — внешний запрос.
Подзапросы могут выполняться в разных частях запроса.
Если подзапрос записать в блоке FROM, то SELECT выберет данные из таблицы, полученной в результате работы подзапроса. Имя этой таблицы указывают во внутреннем запросе, к её столбцам обращаются во внешнем. Подзапрос записывают в круглых скобках:
Скопировать кодSQL
SELECT
ПОДЗАПРОС_1.название_столбца,
ПОДЗАПРОС_1.название_столбца_2
FROM
(SELECT
название_столбца,
название_столбца_2
FROM
название_таблицы
WHERE
название_столбца = значение) AS ПОДЗАПРОС_1;
Напишем запрос, который подсчитает среднее количество проставленных оценок по жанрам книг. Нужно вызвать две агрегирующие функции: COUNT() найдёт количество, AVG() подсчитает среднее. Однако написать SELECT AVG(COUNT(rating)) не выйдет — получим сообщение об ошибке: «агрегирующие функции не могут быть вложенными» (англ. ERROR: aggregate function calls cannot be nested).
Сначала нужно вызвать актёров на пробы, а потом просмотреть пробы и выбрать лучшего. Найдём количество оценок по жанрам в подзапросе, а во внешнем запросе рассчитаем среднее полученных значений:
Скопировать кодSQL
SELECT
AVG(Sub.count_rating) AS avg_count_rating
FROM
(SELECT
COUNT(rating) AS count_rating
FROM
books
GROUP BY genre) AS Sub;
Разберём результат работы внутреннего запроса:
В внутреннем запросе подсчитали число оценок книг каждого жанра и сохранили его в поле count_rating. К таблице-результату работы подзапроса теперь можно обратиться во внешнем запросе.
Вызовем столбец count_rating таблицы Sub (от англ. subquery, «подзапрос») и найдём среднее функцией AVG():
Скопировать кодSQL
3
Что это значит? Таблица содержит в среднем по 3 оценки, проставленные каждому жанру.
Внутренние запросы могут понадобиться в разных блоках внешнего запроса. Например, устроим подзапрос в блоке WHERE. Тогда выберутся данные из столбца со значениями, сгенерированными в результате работы подзапроса:
Скопировать кодSQL
SELECT
название_столбца,
название_столбца_1
FROM
название_таблицы
WHERE
название_столбца =
(SELECT
столбец_1
FROM
название_таблицы_2
WHERE
столбец_1 = значение);
Дополним шаблон конструкцией IN, чтобы собирать данные из нескольких столбцов:
Скопировать кодSQL
SELECT
название_столбца,
название_столбца_1
FROM
название_таблицы
WHERE
название_столбца IN
(SELECT
столбец_1
FROM
название_таблицы_2
WHERE
столбец_1 = значение_1 OR столбец_1 = значение_2);
Пример: добавим к таблице books таблицу genre с кодами жанров и их названиями:
Выберем книги жанра «Роман» из таблицы books:
Скопировать кодSQL
SELECT
name,
genre
FROM
books
WHERE
genre =
(SELECT
id
FROM
genre
WHERE
name ='Роман');
Подзапрос указывает, что нужно выбрать из таблицы genre те id, которым соответствует жанр — «Роман». Результат подзапроса: 1. Лишь одно значение из таблицы genre означает романы.
Перейдём к внешнему запросу. Он выбирает названия и жанры из таблицы books. Но только те, чей жанр равен результату внутреннего запроса, то есть единице.
Результат работы внешнего запроса:
Напишем похожий запрос. Выберем не только романы, но и повести с драмами. Вот где пригодится IN:
Скопировать кодSQL
SELECT
name,
genre
FROM
books
WHERE
genre IN
(SELECT
id
FROM
genre
WHERE
name IN ( 'Роман', 'Драма', 'Повесть' ));
Конструкция IN во внутреннем запросе выбирает id из таблицы жанров с названиями 'Роман', 'Драма' или 'Повесть'. В результате получим три id: 1, 9, 5. Эти значения передаются во внешний запрос.
Внешний запрос сообщает, что нужно выбрать названия и жанры из таблицы books, где жанры равны значениям из результирующего списка внутреннего запроса. То есть, 1, 9 или 5.