DML. UNION

Сегодня рассмотрим возможности SQL по объединению результатов двух самостоятельных запросов. Т.е. Возможность представить строки двух и более таблиц (полученных в результате нескольких запросов) в виде одной объединенной таблицы. Например, в результате запросов, вернувших 10 и 20 строк, в итоге должна получиться одна общая выборка на 30 строк.

Такая функциональность обычно нужна в двух случаях:

1. Объединение результатов из нескольких разных таблиц. Например, мы недавно создавали таблицы  passenger_male и passenger_female, которые содержат одинаковые колонки, но разные записи. Такой сценарий относительно редок, но более понятен интуитивно;
2. Объединение результата несокльких запросов  к одной таблице, возвращающий два совершенно разных или пересекающихся множества. Как правило, такие запросы можно представить в виде одного запроса, но он может быть менее производительным, нежели два запроса с последующим объединением. Более частый сценарий, но его использование требует некоторых навыков по работе с планом запроса, чтобы понимать, когда два запроса позволят оптимизировать взаимодействие с БД и как именно для этого необходимо разделить изначальный общий запрос.

Теперь, когда актуальность более-менее понятна, разберемся с синтаксисом.

select * from passenger_male
union
select * from passenger_female;

В данном случае мы берем все колонки и все записи из таблицы passenger_male и все колонки и все записи из таблицы passenger_female, после чего объединяем их в одну общую выборку.

За объединение выборок отвечает оператор UNION.

Стоит отметить, что для того, чтобы запрос работал, выражения (выбираемые колонки) двух запросов должны совпадать по количеству и типам данных (в порядке перечисления).

Так, если мы попытаемся объединить passenger_female и passenger - будет ошибка, потому что первая таблица содержит колонку created, но не содержит колонки male, а вторая - наоборот.

Попробуем доработать запрос, чтобы он возвращал все записи passenger_female  и passenger. Не совпадающие колонки просто исключим:

select id, first_name, last_name, birth_date, last_purchase, 
  favorite_airports 
  from passenger_female
union
select id, first_name, last_name, birth_date, last_purchase, 
  favorite_airports 
  from passenger;

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

Чтобы дублирующиеся строки не объединялись - можем использовать другой оператор - UNION ALL:

select id, first_name, last_name, birth_date, last_purchase, 
  favorite_airports
  from passenger_female
union all
select id, first_name, last_name, birth_date, last_purchase, 
  favorite_airports 
  from passenger;

Теперь дубликаты дублируются:)

Также мы можем доработать запрос, чтобы он возвращал, например, колонку male, которая отсутствует в passenger_female. Очевидно, что все записи в этой таблице относятся к женскому полу, поэтому просто проставим false в качестве значения для этой колонки. И укажем алиас, чтобы имя колонки отображалось корректно в результирующей выборке:

select id, first_name, last_name, birth_date, last_purchase, 
  favorite_airports, false male 
  from passenger_female
union all
select id, first_name, last_name, birth_date, last_purchase, 
  favorite_airports, male 
  from passenger;

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

В целом, на этом можно завершать знакомство с базовым синтаксисом. Но зачастую требуется сделать дополнительные действия с результирующей выборкой - те же сортировка, пагинация, агрегация или даже дополнительная фильтрация. Последнее нежелательно (если речь о WHERE, а не HAVING), поскольку мы работаем уже с результирующей таблицей, а не изначальными, из-за чего SQL не может использовать базовые инструменты оптимизации для фильтрации (в первую очередь - индексы, с которыми мы познакомимся позже).

Для того, чтобы получить доступ к этой функциональности - необходимо сделать SELECT’ы с UNION в виде подзапроса, а операции над результирующей выборкой вынести в основной запрос.

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

select * from (
  select id, first_name, last_name, birth_date, last_purchase, 
    favorite_airports, false male 
    from passenger_female
  union all
  select id, first_name, last_name, birth_date, last_purchase, 
    favorite_airports, male 
    from passenger
) p
order by first_name, id limit 3 offset 0;

Такой запрос будет работать корректно, но стоит обратить внимание, что он выбирает все записи в каждой из таблиц, а лишь потом сортирует и пагинирует. Если ваши таблицы содержат миллионы записей - это может оказаться несколько избыточным и может иметь смысл добавить аналогичную сортировку и пагинацию в каждом из SELECT’ов в подзапросе - тогда основной запрос будет работать максимум с pageSize*2 записей, т.е., в нашем случае - с 6 записями. При этом каждый из вложенных SELECT-запросов стоит обернуть в скобки - UNION не испытывает желания применяться после указание OFFSET:)

select * from (
  (select id, first_name, last_name, birth_date, last_purchase, 
    favorite_airports, false male 
    from passenger_female
    order by first_name, id limit 3 offset 0)
  union all
  (select id, first_name, last_name, birth_date, last_purchase, 
    favorite_airports, male 
    from passenger
    order by first_name, id limit 3 offset 0)
) p
order by first_name, id limit 3 offset 0;

В целом, UNION вряд ли будет популярным инструментом в вашей работе, но в ряде задач он не заменим. 

С теорией на сегодня все!

Переходим к практике:

Задача 1

Верните все имена и фамилии из таблиц passenger_female, passenger_male и passenger. Исключите дубликаты (не только возникающие при объединении выборок).

Задача 2

Получите всю информацию о трех самых старых пассажирах, имеющих хотя бы один любимый аэропорт, из объединенных таблиц passenger_female, passenger_male. 

Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)

Канал: https://t.me/ViamSupervadetVadens

Мой тг: https://t.me/ironicMotherfucker

Дорогу осилит идущий!