DML. Подзапросы
Дорогу осилит идущийМы уже достаточно далеко продвинулись в изучении способов извлечения данных из таблиц. В рамках данной статьи рассмотрим синтаксис подзапросов, позволяющий вызвать один запрос внутри другого.
Подзапросы (они же – вложенные запросы) – мощный механизм, позволяющий обойти ряд ограничений при поиске. Мы с ними, на самом деле, уже раз сталкивались (и еще несколько раз – не столкнулись лишь потому что я осознанно не подсвечивал подобные моменты). Из последнего – ограничение на вызов одной агрегатной функции внутри другой.
Итак, подзапросы. Как уже упоминалось в рамках одного из уроков, подзапросом называют SELECT-запрос, вложенный внутри другого запроса.
Подзапросы в WHERE
Подзапросы могут использоваться по отношению к различным частям основного запроса. Начнем с самого популярного вариант: подзапрос используется к одном из WHERE-условий.
Например:
select * from passenger where id in (select id from passenger where male);
В данном случае происходит получение всех записей из таблицы, в которых id равен одному из значений, полученных из подзапроса. Подзапрос, в свою очередь возвращает id всех пассажиров-мужчин.
Данный пример вполне нагляден, хоть и лишен практического смысла – мы могли бы просто использовать условие из подзапроса в WHERE-блоке основного запроса. Однако такая возможность есть далеко не всегда.
Например, давайте попробуем вернуть id пассажиров, у которых дата рождения совпадает с самой поздней датой рождения среди всех пассажиров.
Вероятно, если вы попытаетесь это сделать, получится примерно следующее:
select id from passenger having max(birth_date) = birth_date;
Но проблема в том, что мы не можем использовать в этом запросе id и birth_date, не добавив их в блок GROUP BY. А если добавить – получим список всех id в таблице – ведь сначала будет произведена группировка по этим колонкам, а потом уже для каждой группы будет выбрана максимальная дата рождения (которая и так всего одна в каждой из групп – мы ведь по ней только что сгруппировали).
Зато с использованием подзапроса мы можем сделать примерно следующее:
select id from passenger where birth_date = (select max(birth_date) from passenger);
Кроме того, внутри подзапроса мы можем обращаться к таблице или выражениям из внешнего запроса. Как правило, с использованием алиасов, чтобы избежать двойственности восприятия (например, когда основной запрос и подзапрос обращаются к одной и той же таблице).
Придумать простой и не лишенный смысла SELECT-запрос, ограничиваясь использованием одной таблицы, оказалось задачей не тривиальной (если у кого-то такой есть – пишите, используем здесь в качестве примера), поэтому пример направлен исключительно на демонстрацию синтаксиса. Более очевидные и логичные сценарии использования разберем в следующих статьях.
Итак, попытаемся извлечь id пассажиров-мужчин, чья дата рождения совпадает с максимальной, среди всех пассажиров-мужчин:
select p.id from passenger p where male and p.birth_date = ( select max(birth_date) from passenger where male = p.male );
В данном случае мы делаем фильтрацию по полу в основном запросе (оставляем только мужчин), после чего в подзапросе ищем максимальную дату рождения среди пассажиров-мужчин и фильтруем основную выборку по совпадению с этой датой.
И, наконец, мы, безусловно, можем использовать подзапросы внутри подзапросов. Пример снова утрированный, призванный продемонстрировать синтаксис:
select * from passenger
where id in (
select id from passenger
where male and id in (
select id from passenger where birth_date < '2000-01-01'
)
);
Также могут использоваться несколько не связанных между собой запросов. Например, несколько разных WHERE-условий в основном запросе будут использовать собственные подзапросы. Но этот факт, полагаю, очевиден.
Последние два сценария делают запрос более сложным и тяжелым для восприятия. Через несколько уроков мы познакомимся с Common Table Expressions и оператором WITH, который позволит вернуть подобным запросам читабельность. Хотя бы отчасти.
Подзапросы в операторах фильтрации
В рамках примеров выше можно увидеть, что подзапрос, в общем-то, может быть применен к любому оператору фильтрации. Но большинство из них будут ожидать, что подзапрос вернет единственное значение. Некоторые же (из знакомых нам – IN) способны работать и с большим числом значений. К тому же, на данном этапе подобные подзапросы сохраняют другое ограничение – из них должна возвращаться таблица с единственной колонкой.
Изначально, в рамках этого урока я предполагал продемонстрировать работу упоминаемых ранее операторов EXISTS, ANY и ALL, но лучше будет разобрать их после того, как выйдем за пределы одной таблицы – это позволит дать более живые и интересные примеры и задачи.
Подзапросы и FROM
Другой способ использования подзапросов заключается в том, что результирующая выборка основного запроса строится не на основании таблицы, а на базе выборки другого (вложенного) запроса:
select * from (select * from passenger where male) p where birth_date > '1970-01-01';
В данном случае мы делаем выборку пассажиров, родившихся после 01.01.1970, из результатов (выборки) подзапроса, который возвращает всех пассажиров-мужчин. Обратите внимание, при таком использовании мы обязаны использовать алиас для обозначения результатов подзапроса.
Подобный подход позволяет, в том числе, эмулировать вызов агрегатной функции внутри другой агрегатной функции. Точнее, агрегировать уже агрегированные данные:
select max(p.count) from (select count(*) count, male from passenger group by male) p;
В данном случае мы получаем максимально число пассажиров одного пола.
Таким образом, подзапрос может выступить не только источником данных для фильтрации, но и источником данных для результирующей выборки.
Заключение
Подзапросы – очень нужный и мощный инструмент, с которым, так или иначе, приходится работать большинству людей, использующих SQL.
Однако стоит помнить, что подзапросы – достаточно дорогое, с точки зрения производительности, удовольствие. Имеет смысл избегать их использования, если можно обойтись без них (как, например, в самом первом примере этой статьи). Если же нет уверенности, что подход без использования подзапроса более оптимальный – стоит обратиться к плану выполнения каждого из запросов и выбрать более оптимальный.
Планировщик – необходимый инструмент для оптимизации запросов, но в рамках курса мы его изучать не будем – это явно не уровень джуниора. Хорошая новость в том, что данная тема легко гуглится, плохая – тема объемная и далеко не самая простая, по крайней мере, на данном этапе знакомства с SQL:)
С теорией на сегодня все!
Переходим к практике:
Задача 1
Найти всех пассажиров, чье имя и фамилия содержат хотя бы одну букву "a", и у которых дата рождения меньше 1990 года.
Эту задачу можно решить и без подзапроса. Поэтому предлагаю продумать хотя бы два возможных варианта решения.
Задача 2
Найти топ-3 аэропорта, которые являются самыми популярными среди пассажиров (с наибольшим количеством упоминаний в столбце favorite_airports).
Для получения набора единичных элементов из массива существует агрегатная функция unnest().
Задача 3*
Найти количество мужчин и женщин среди пассажиров.
Пример вывода:
Male | Female
-------------------------
5 | 7
Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)
Канал: https://t.me/ViamSupervadetVadens
Мой тг: https://t.me/ironicMotherfucker
Дорогу осилит идущий!