Данные в нормализованной базе разложены по отдельным таблицам, и почти любой осмысленный отчёт требует собрать их обратно. Билет лежит в одной таблице, перелёт - в другой, рейс - в третьей, аэропорт - в четвёртой. Соединение (JOIN) - это операция, которая по условию связывает строки из нескольких таблиц в одну широкую строку результата. В этом уроке разберём все виды JOIN в postgresql, чем ON отличается от USING, как соединить цепочку из четырёх таблиц демобазы и почему именно outer join чаще всего ломает отчёты из-за NULL.
Как это работает
Соединение всегда работает попарно: берётся строка из левой таблицы и проверяется условие соединения с каждой строкой правой. Если условие истинно - строки склеиваются в одну. Так формально работает любой JOIN, а уж как именно планировщик это исполнит (вложенным циклом, хешем или слиянием) - его дело, мы лишь описываем что хотим получить.
INNER JOIN оставляет только пары, где условие совпало. Если для строки слева пары справа нет - она просто выпадает из результата. Это поведение по умолчанию: слово INNER можно опускать, просто JOIN.
Внешние соединения (OUTER) сохраняют строки даже без пары. LEFT JOIN гарантирует, что каждая строка левой таблицы попадёт в результат хотя бы раз; если пары справа не нашлось, столбцы правой таблицы заполняются значением NULL. RIGHT JOIN - то же самое, но сохраняется правая таблица. FULL OUTER JOIN сохраняет несовпавшие строки с обеих сторон. На практике RIGHT почти не пишут: его всегда можно превратить в LEFT, поменяв таблицы местами, а читать слева направо привычнее.
CROSS JOIN - это декартово произведение: каждая строка слева соединяется с каждой строкой справа без всякого условия. Если в таблицах 100 и 50 строк, получится 5000. Чаще всего огромный CROSS JOIN возникает случайно - когда забыли условие соединения.
Self-join - это соединение таблицы самой с собой. Чтобы различать два экземпляра одной таблицы, им дают разные псевдонимы (алиасы). Типичные задачи: сравнить строки одной таблицы между собой или пройти по иерархии типа сотрудник-руководитель.
Условие соединения задают двумя способами. ON принимает любое логическое выражение и подходит всегда. USING - сокращение для частого случая, когда столбцы в обеих таблицах называются одинаково: USING (flight_id) эквивалентно ON a.flight_id = b.flight_id, но при этом склеивает два одноимённых столбца в один общий в выводе. Это важная деталь: после USING нельзя писать table.flight_id - столбец становится общим и обращаться к нему надо просто по имени.
SQL и примеры
Соберём по билету его перелёты - связь tickets и ticket_flights по ticket_no. Это INNER JOIN, в результат попадут только билеты, у которых есть перелёты:
Код: Выделить всё
SELECT t.ticket_no, t.passenger_name, tf.flight_id, tf.amount
FROM tickets t
JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
LIMIT 10;Код: Выделить всё
SELECT t.passenger_name,
f.flight_no,
dep.city AS departure_city
FROM tickets t
JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
JOIN flights f ON f.flight_id = tf.flight_id
JOIN airports dep ON dep.airport_code = f.departure_airport
LIMIT 10;Код: Выделить всё
SELECT f.flight_no,
dep.city AS from_city,
arr.city AS to_city
FROM flights f
JOIN airports dep ON dep.airport_code = f.departure_airport
JOIN airports arr ON arr.airport_code = f.arrival_airport
LIMIT 10;Код: Выделить всё
SELECT tf.ticket_no, tf.flight_id
FROM ticket_flights tf
LEFT JOIN boarding_passes bp
ON bp.ticket_no = tf.ticket_no
AND bp.flight_id = tf.flight_id
WHERE bp.boarding_no IS NULL;То же соединение через USING выглядит короче, так как имена столбцов совпадают в обеих таблицах:
Код: Выделить всё
SELECT *
FROM ticket_flights
LEFT JOIN boarding_passes USING (ticket_no, flight_id)
LIMIT 10;- Условие на правую таблицу outer join в WHERE превращает LEFT JOIN обратно в INNER. Если написать LEFT JOIN boarding_passes bp ... WHERE bp.seat_no = '1A', то строки с NULL отфильтруются, и эффект сохранения левой таблицы пропадёт. Условие на правую таблицу пишите в ON, а не в WHERE.
- Сравнение с NULL через знак равенства всегда даёт неизвестность, а не истину. Чтобы найти несовпавшие строки после outer join, используйте IS NULL, а не = NULL.
- Забытое условие соединения молча даёт CROSS JOIN. Запрос отработает, но вернёт миллионы строк и подвесит сервер. Всегда проверяйте, что у каждого JOIN есть ON или USING.
- COUNT(*) после LEFT JOIN считает и строки-пустышки с NULL. Если нужно посчитать реальные совпадения, считайте конкретный непустой столбец: COUNT(bp.boarding_no).
- USING склеивает одноимённые столбцы в один. После USING (flight_id) обращение f.flight_id вызовет ошибку - пишите просто flight_id.
- Дубли строк после JOIN - частый сюрприз. Если справа на одну левую строку приходится несколько пар, левая строка размножится. Это не баг соединения, а связь один-ко-многим.
- Подключитесь к демобазе: psql demo
- Соедините flights и aircrafts по aircraft_code (INNER JOIN) и выведите flight_no и model самолёта для 10 рейсов.
- Перепишите это соединение через USING (aircraft_code) и убедитесь, что в выводе aircraft_code остался один столбец.
- Сделайте LEFT JOIN flights к aircrafts и найдите рейсы, у которых aircraft_code не сопоставился с моделью (model IS NULL).
- Постройте цепочку tickets -> ticket_flights -> flights -> airports (вылет) и выведите passenger_name и город вылета для рейсов со статусом 'Arrived'.
- Намеренно уберите условие в одном JOIN, выполните EXPLAIN (без ANALYZE) и посмотрите на оценку числа строк - так выглядит случайный CROSS JOIN.
- Сравните два аэропорта в одном рейсе self-join-подобным двойным соединением airports и выведите пары from_city - to_city.
- Чем результат INNER JOIN отличается от LEFT JOIN, если у части левых строк нет пары справа?
- Почему условие на правую таблицу, помещённое в WHERE, отменяет смысл LEFT JOIN, а в ON - нет?
- В каких случаях USING удобнее ON и какое ограничение он накладывает на обращение к столбцам?
- Как с помощью outer join и проверки на NULL найти строки, у которых нет соответствия в другой таблице?
- Почему RIGHT JOIN на практике почти не используют и чем его заменяют?
- Откуда после соединения берутся дубликаты строк и как это связано со связью один-ко-многим?
