Запросы на выборку данных и соединения таблиц

Все примеры в лабораторной выполняются на учебной базе demo, развёрнутой в лабораторной работе 3.

Подключитесь к базе demo и установите схему поиска

\c demo
SET search_path TO bookings;

Убедитесь, что таблицы схемы bookings доступны

\dt

Версия демонстрационной базы. На сайте postgrespro.ru/education/demodb с 2025 года доступна обновлённая редакция демонстрационной базы с изменённой структурой (таблица aircrafts переименована в airplanes, ticket_flights — в segments, столбец amount — в price и т.д.). Все запросы в этой работе написаны для редакции 2017 года (demo_small.sql), которая используется начиная с лабораторной работы 3. Если вы работаете с новой версией, необходимо адаптировать имена таблиц и столбцов

В качестве основного справочного и тренировочного ресурса в этой работе используется sql-ex.ru — тренажёр с рейтинговой системой, подробными теоретическими разделами и проверяемыми упражнениями. Ссылки на конкретные разделы теории и рекомендуемые упражнения расставлены по тексту в конце каждого раздела. Общая страница справки: sql-ex.ru/help

Помимо sql-ex, полезны следующие площадки:

• pgexercises.com — тренажёр, ориентированный на PostgreSQL, с задачами по соединениям, агрегации и работе с датами
• SQLZoo — интерактивный учебник с задачами возрастающей сложности, широко используется в курсах по всему миру
• SQLBolt — пошаговые уроки с интерактивными упражнениями
• Stepik — русскоязычные курсы по SQL (например, «Интерактивный тренажёр по SQL»)

Расширенные возможности SELECT

В лабораторной работе 3 были рассмотрены базовые конструкции SELECT, WHERE и ORDER BY. Здесь спектр инструментов расширяется возможностями, которые постоянно используются при написании аналитических запросов

DISTINCT — ключевое слово, указываемое сразу после SELECT, которое исключает из результирующего набора повторяющиеся строки. Без него каждая строка таблицы, прошедшая фильтрацию WHERE, попадает в результат, даже если значения во всех запрашиваемых столбцах уже встречались ранее. На практике DISTINCT часто применяется, когда нужно получить не сами записи, а перечень уникальных значений определённого атрибута

SELECT DISTINCT city
FROM airports
ORDER BY city

Этот запрос возвращает список городов, в которых расположены аэропорты, без повторений. Если в одном городе несколько аэропортов, без DISTINCT такой город появился бы в результате несколько раз

DISTINCT применяется ко всей строке результата, а не к одному столбцу. Если в SELECT перечислены два столбца, уникальность проверяется по их комбинации

SELECT DISTINCT departure_airport, arrival_airport
FROM flights
ORDER BY departure_airport, arrival_airport

Этот запрос формирует список всех маршрутов — уникальных пар «откуда — куда»

Предикаты BETWEEN и IN. Помимо операторов сравнения (=, <, > и т.д.), SQL предоставляет два удобных предиката для проверки диапазонов и принадлежности множеству

BETWEEN проверяет, попадает ли значение в заданный диапазон, включая обе границы. Выражение x BETWEEN a AND b эквивалентно x >= a AND x <= b

SELECT model, range
FROM aircrafts
WHERE range BETWEEN 3000 AND 6000
ORDER BY range

Среднемагистральные самолёты с дальностью от 3000 до 6000 км включительно

IN проверяет, совпадает ли значение с одним из элементов списка. Это более компактная запись, чем цепочка OR

SELECT flight_no, status, scheduled_departure
FROM flights
WHERE status IN ('Delayed', 'Cancelled')
ORDER BY scheduled_departure
LIMIT 10

Задержанные и отменённые рейсы. Без IN пришлось бы писать WHERE status = 'Delayed' OR status = 'Cancelled' — при длинных списках это становится громоздким

Теория и практика на sql-ex.ru. Разделы: Предикат BETWEEN, Предикат IN. Рекомендуемые упражнения: 1, 5, 8, 23, 38

Логические операторы AND, OR, NOT. Условия в WHERE можно комбинировать с помощью логических операторов. AND требует истинности обоих условий, OR — хотя бы одного, NOT инвертирует результат

SELECT flight_no, scheduled_departure, status
FROM flights
WHERE departure_airport = 'SVO'
  AND status <> 'Cancelled'
  AND scheduled_departure >= '2017-08-01'
  AND scheduled_departure <  '2017-09-01'
ORDER BY scheduled_departure
LIMIT 10

Все некоторые рейсы из Шереметьево за август 2017 года

При сочетании AND и OR важен приоритет: AND выполняется раньше OR. Без скобок выражение A OR B AND C интерпретируется как A OR (B AND C). Чтобы избежать неоднозначности, рекомендуется расставлять скобки явно

SELECT flight_no, departure_airport, status
FROM flights
WHERE (departure_airport = 'SVO' OR departure_airport = 'DME')
  AND status = 'Arrived'
LIMIT 10

Рейсы из Шереметьево или Домодедово со статусом «прибыл». Без скобок результат был бы другим: все рейсы из SVO плюс только прибывшие рейсы из DME

SQL использует трёхзначную логику: помимо TRUE и FALSE существует UNKNOWN (неизвестно), которое возникает при любой операции с NULL. Выражение NULL AND TRUE даёт UNKNOWN, а WHERE пропускает только строки, для которых условие вычисляется в TRUE. Это означает, что строки с UNKNOWN исключаются из результата так же, как и строки с FALSE

Теория и практика на sql-ex.ru. Раздел: Булевы операторы AND, OR, NOT и трёхзначная логика

Вычисляемые столбцы и выражение CASE. В SELECT можно не только извлекать существующие столбцы таблицы, но и создавать новые, вычисляемые «на лету». Арифметические выражения, вызовы функций и условная логика через CASE — стандартные приёмы формирования результирующего набора

SELECT flight_no,
       scheduled_departure,
       scheduled_arrival,
       scheduled_arrival - scheduled_departure AS duration
FROM flights
ORDER BY duration DESC
LIMIT 10

Здесь duration — вычисляемый столбец, представляющий собой разницу двух временных меток. Ключевое слово AS задаёт псевдоним (alias). Без него столбец в результате останется без имени, что затрудняет дальнейшую работу с ним

Выражение CASE позволяет добавить условную логику прямо внутрь запроса. Синтаксически CASE напоминает конструкцию if-elif-else из языков программирования: ветки проверяются последовательно сверху вниз, и первое сработавшее условие определяет результат. Если ни одно условие не выполнено, возвращается значение из ELSE, а при его отсутствии — NULL

SELECT flight_no,
       scheduled_arrival - scheduled_departure AS duration,
       CASE
           WHEN scheduled_arrival - scheduled_departure < interval '3 hours'
               THEN 'короткий'
           WHEN scheduled_arrival - scheduled_departure < interval '6 hours'
               THEN 'средний'
           ELSE 'длительный'
       END AS flight_category
FROM flights
LIMIT 15

Теория и практика на sql-ex.ru. Раздел: Оператор CASE. Рекомендуемые упражнения: 31, 32, 47, 53, 54

Шаблоны поиска: LIKE. Оператор LIKE проверяет строку на соответствие шаблону с использованием двух специальных символов: % обозначает любую последовательность символов (включая пустую), а _ — ровно один произвольный символ

SELECT airport_name, city
FROM airports
WHERE city LIKE 'М%'

Запрос вернёт аэропорты в городах, название которых начинается на «М»: Москва, Магадан, Мурманск и другие

SELECT airport_name, city
FROM airports
WHERE airport_name LIKE '%ово'

Аэропорты, названия которых заканчиваются на «ово»

Особенность PostgreSQL. Помимо стандартного LIKE, PostgreSQL поддерживает оператор ILIKE, который выполняет сравнение без учёта регистра символов. В стандарте SQL такой оператор отсутствует. Аналогичный результат в стандартном SQL достигается через приведение к одному регистру

-- Стандартный SQL
SELECT airport_name, city
FROM airports
WHERE LOWER(city) LIKE '%петербург%'

-- PostgreSQL
SELECT airport_name, city
FROM airports
WHERE city ILIKE '%петербург%'

Теория и практика на sql-ex.ru. Раздел: Предикат LIKE. Рекомендуемые упражнения: 44

Ограничение результата: LIMIT и OFFSET. Конструкция LIMIT задаёт максимальное число возвращаемых строк, а OFFSET пропускает указанное количество строк от начала результата. Вместе они позволяют реализовать постраничную навигацию

SELECT flight_no, scheduled_departure, status
FROM flights
ORDER BY scheduled_departure
LIMIT 10

Первые 10 рейсов по дате вылета

SELECT flight_no, scheduled_departure, status
FROM flights
ORDER BY scheduled_departure
LIMIT 10 OFFSET 10

Следующие 10 рейсов — вторая «страница»

Без ORDER BY порядок строк в результирующем наборе не определён, и LIMIT может вернуть произвольный набор строк, который к тому же будет меняться от запуска к запуску. На практике LIMIT почти всегда используется совместно с ORDER BY

Особенность PostgreSQL. LIMIT и OFFSET широко поддерживаются в PostgreSQL, MySQL и SQLite, однако не входят в стандарт SQL. Стандартный синтаксис выглядит иначе: FETCH FIRST 10 ROWS ONLY. PostgreSQL поддерживает оба варианта

В ORDER BY допускается указывать не имя столбца, а его порядковый номер в списке SELECT. Это бывает удобно, когда столбец является вычисляемым выражением без псевдонима

SELECT departure_airport, arrival_airport, count(*)
FROM flights
GROUP BY departure_airport, arrival_airport
ORDER BY 3 DESC
LIMIT 5

Здесь ORDER BY 3 означает сортировку по третьему столбцу результата, то есть по count(*). Такой стиль компактнее, но менее читаем — при изменении порядка столбцов в SELECT сортировка «сломается» без видимой причины. Поэтому в рабочем коде предпочтительнее использовать псевдонимы

Особенность PostgreSQL. При сортировке по столбцу, содержащему NULL, возникает вопрос: куда помещать пустые значения — в начало или в конец? По умолчанию PostgreSQL располагает NULL после всех ненулевых значений при сортировке по возрастанию (ASC) и перед ними при сортировке по убыванию (DESC). Это поведение можно изменить явно:

SELECT flight_no, actual_departure
FROM flights
ORDER BY actual_departure NULLS FIRST
LIMIT 10

Конструкции NULLS FIRST и NULLS LAST входят в стандарт SQL, но поддерживаются не всеми СУБД

Обработка NULL. Значение NULL обозначает отсутствие данных. Обычное сравнение = NULL не работает, потому что результатом любой операции с NULL является NULL, а не TRUE или FALSE. Для проверки отсутствия или наличия значения используются специальные конструкции IS NULL и IS NOT NULL

SELECT flight_no, actual_departure, status
FROM flights
WHERE actual_departure IS NULL
  AND status = 'Cancelled'
LIMIT 10

Отменённые рейсы, которые так и не вылетели

Функция COALESCE возвращает первое ненулевое значение из списка аргументов. Она удобна для подстановки значения по умолчанию вместо NULL — например, при формировании отчётов

SELECT flight_no,
       COALESCE(actual_departure::text, '—') AS departure_fact
FROM flights
WHERE status IN ('Scheduled', 'Cancelled')
LIMIT 10

Функция NULLIF(a, b) выполняет обратное действие: если a равно b, возвращает NULL, иначе — a. Это полезно для защиты от деления на ноль и для подавления «мусорных» значений

SELECT flight_no,
       actual_arrival - actual_departure AS flight_time,
       NULLIF(status, 'Scheduled') AS meaningful_status
FROM flights
LIMIT 10

Для рейсов со статусом 'Scheduled' столбец meaningful_status будет содержать NULL вместо самого значения. COALESCE и NULLIF часто используются совместно: COALESCE(x / NULLIF(y, 0), 0) — безопасное деление, возвращающее 0 при нулевом делителе

Особенность PostgreSQL. Синтаксис ::text — сокращённая запись приведения типа, специфичная для PostgreSQL. В стандартном SQL используется CAST(actual_departure AS text)

Теория и практика на sql-ex.ru. Разделы: Простой оператор SELECT, DISTINCT, WHERE, Предикат IS NULL, Предикаты сравнения и BETWEEN. Рекомендуемые упражнения: 1, 2, 3, 4, 5, 8, 23, 38, 42

Дополнительно. SQLZoo — SELECT basics | pgexercises.com — Basic queries

Задание

1. Напишите запрос, который классифицирует самолёты по дальности полёта (range): «ближнемагистральный» (до 3000 км), «среднемагистральный» (3000–6000 км), «дальнемагистральный» (свыше 6000 км). Используйте CASE
2. Найдите рейсы, в номере которых вторая буква — «N» (используйте _ в шаблоне LIKE)

Агрегатные функции, группировка и HAVING

Агрегатные функции обобщают множество строк в одно итоговое значение. Без GROUP BY агрегат вычисляется по всей таблице целиком. С GROUP BY таблица предварительно разбивается на группы по значениям указанных столбцов, и агрегатная функция вычисляется для каждой группы отдельно

SQL определяет пять основных агрегатных функций:

Важное различие: COUNT(*) считает строки, а COUNT(столбец) считает только ненулевые значения в указанном столбце. Если столбец содержит NULL, эти строки будут учтены в COUNT(*), но пропущены в COUNT(столбец)

Существует также форма COUNT(DISTINCT столбец), которая возвращает количество уникальных ненулевых значений

Чтобы увидеть, как агрегация работает пошагово, рассмотрим пример на маленькой выборке. Допустим, из таблицы seats мы взяли 6 строк:

Запрос SELECT aircraft_code, fare_conditions, count(*) FROM seats GROUP BY aircraft_code, fare_conditions разбивает эти строки на группы по уникальным комбинациям (aircraft_code, fare_conditions):

• Группа (SU9, Economy): 2 строки
• Группа (SU9, Business): 1 строка
• Группа (773, Economy): 1 строка
• Группа (773, Business): 2 строки

Результат:

Каждая строка результата соответствует одной группе. Внутри группы доступны только агрегатные функции (они «схлопывают» множество строк в одно значение) и столбцы, по которым выполнялась группировка (они одинаковы для всех строк группы). Именно поэтому SELECT не может содержать «свободный» столбец, не входящий в GROUP BY и не обёрнутый в агрегат — СУБД не знала бы, какое из множества значений выбрать

SELECT count(*) AS total_flights,
       count(actual_departure) AS departed,
       count(*) - count(actual_departure) AS not_departed
FROM flights

Этот запрос показывает, сколько рейсов всего, сколько из них фактически вылетели (имеют заполненное поле actual_departure) и сколько — нет. Разница между count(*) и count(actual_departure) наглядно демонстрирует обработку NULL агрегатными функциями

SELECT min(total_amount) AS min_booking,
       max(total_amount) AS max_booking,
       round(avg(total_amount), 2) AS avg_booking
FROM bookings

Минимальная, максимальная и средняя стоимость бронирования по всей таблице

Группировка через GROUP BY. Когда нужны не общие итоги, а итоги по категориям, к запросу добавляется GROUP BY. Все столбцы в SELECT, не обёрнутые в агрегатную функцию, обязаны присутствовать в GROUP BY. Нарушение этого правила приводит к ошибке

SELECT status, count(*) AS cnt
FROM flights
GROUP BY status
ORDER BY cnt DESC

Количество рейсов в каждом статусе

Типичная ошибка — указать в SELECT столбец, который не обёрнут в агрегатную функцию и не перечислен в GROUP BY

-- Ошибка: столбец flight_no не входит в GROUP BY и не агрегирован
SELECT status, flight_no, count(*)
FROM flights
GROUP BY status

PostgreSQL выдаст ошибку: column "flights.flight_no" must appear in the GROUP BY clause or be used in an aggregate function. Смысл ограничения в том, что в каждой группе (например, среди всех рейсов со статусом 'Arrived') существует множество различных значений flight_no, и СУБД не может выбрать из них одно без явного указания — нужен либо агрегат (MIN(flight_no)), либо добавление столбца в GROUP BY

Группировка может быть многоуровневой — по нескольким столбцам одновременно

SELECT aircraft_code,
       fare_conditions,
       count(*) AS seat_count
FROM seats
GROUP BY aircraft_code, fare_conditions
ORDER BY aircraft_code, fare_conditions

Распределение мест по классам обслуживания в каждом типе самолёта

SELECT departure_airport,
       arrival_airport,
       count(*) AS flight_count
FROM flights
GROUP BY departure_airport, arrival_airport
ORDER BY flight_count DESC
LIMIT 10

Десять самых популярных маршрутов (пар «аэропорт отправления — аэропорт прибытия») по числу выполненных рейсов

Фильтрация групп через HAVING. Ключевое различие между WHERE и HAVING состоит в моменте применения: WHERE фильтрует отдельные строки до группировки, а HAVING фильтрует уже сформированные группы после вычисления агрегатов. Попытка поместить агрегатную функцию в WHERE (например, WHERE count(*) > 5) приведёт к синтаксической ошибке

Порядок выполнения предложений оператора SELECT:

1. FROM — определение источника данных
2. WHERE — фильтрация строк
3. GROUP BY — формирование групп
4. HAVING — фильтрация групп
5. SELECT — формирование результирующего набора
6. ORDER BY — сортировка результата

SELECT city, count(*) AS airport_count
FROM airports
GROUP BY city
HAVING count(*) > 1
ORDER BY airport_count DESC

Города, в которых расположено более одного аэропорта. Условие count(*) > 1 невозможно поместить в WHERE, потому что на момент работы WHERE агрегат ещё не вычислен

SELECT fare_conditions,
       count(*)          AS ticket_count,
       min(amount)       AS min_price,
       max(amount)       AS max_price,
       round(avg(amount), 2) AS avg_price
FROM ticket_flights
GROUP BY fare_conditions
ORDER BY avg_price

Статистика стоимости перелётов по классам обслуживания: количество проданных билетов, минимальная, максимальная и средняя цена

SELECT departure_airport,
       count(*) AS flight_count
FROM flights
GROUP BY departure_airport
HAVING count(*) > 500
ORDER BY flight_count DESC

Аэропорты с наибольшим числом отправлений, у которых суммарное количество рейсов превышает 500

HAVING может содержать несколько условий, объединённых через AND или OR

SELECT aircraft_code,
       count(*) AS total_seats,
       count(*) FILTER (WHERE fare_conditions = 'Business') AS business_seats
FROM seats
GROUP BY aircraft_code
HAVING count(*) > 100
ORDER BY total_seats DESC

Особенность PostgreSQL. Конструкция FILTER (WHERE ...) позволяет вычислить агрегат только по подмножеству строк внутри группы. Это расширение стандарта SQL:2003, которое поддерживается в PostgreSQL, но отсутствует во многих других СУБД. В стандартном SQL аналогичный результат достигается через CASE:

-- Стандартный SQL
SELECT aircraft_code,
       count(*) AS total_seats,
       SUM(CASE WHEN fare_conditions = 'Business' THEN 1 ELSE 0 END) AS business_seats
FROM seats
GROUP BY aircraft_code
HAVING count(*) > 100
ORDER BY total_seats DESC

При работе с GROUP BY полезно помнить, что NULL обрабатывается как отдельное значение: все строки с NULL в столбце группировки попадают в одну группу

Теория и практика на sql-ex.ru. Разделы: Агрегатные функции, GROUP BY, HAVING, Порядок выполнения предложений SELECT. Рекомендуемые упражнения: 10, 11, 12, 14, 15, 20, 22, 33, 43, 51, 52

Дополнительно. SQLZoo — SUM and COUNT | pgexercises.com — Aggregation

Задание

1. Найдите модели самолётов, у которых более 200 посадочных мест (таблица seats, группировка по aircraft_code, фильтрация через HAVING)
2. Для каждого маршрута (пара departure_airport — arrival_airport) посчитайте количество рейсов и среднюю стоимость перелёта (потребуется JOIN с ticket_flights). Выведите только маршруты с более чем 20 рейсами

Соединения таблиц

В нормализованной базе данных информация распределена по нескольким таблицам: рейсы хранятся в flights, названия аэропортов — в airports, данные пассажиров — в tickets, стоимость перелётов — в ticket_flights. Чтобы получить содержательный результат, объединяющий данные из разных таблиц (например, «из какого города вылетает рейс» или «какой пассажир летит каким рейсом»), необходимо соединить таблицы по ключу связи

Соединение — это операция, которая сопоставляет строки из двух или более таблиц по заданному условию и формирует результирующий набор, содержащий столбцы из всех участвующих таблиц. Условие соединения указывается после ключевого слова ON

Четыре типа соединений и операции над множествами. Четыре основных типа JOIN удобно сопоставить с операциями теории множеств. Пусть A — множество строк левой таблицы, B — правой, а совпадение по ключу определяет область пересечения

Аналогия с множествами является упрощением. В теории множеств элементы уникальны, тогда как в таблицах строки могут повторяться, и одна строка левой таблицы может сопоставиться с несколькими строками правой (и наоборот). Более того, INNER JOIN может вернуть больше строк, чем содержалось в любой из исходных таблиц, если связь между ними является связью «многие ко многим». Тем не менее диаграммы Венна остаются полезным мнемоническим инструментом для запоминания поведения каждого типа соединения

Демонстрация на простых данных. Чтобы увидеть разницу между типами соединений на конкретных строках, создадим две маленькие таблицы

CREATE TABLE join_demo_a (id integer, label text);
CREATE TABLE join_demo_b (id integer, label text);

INSERT INTO join_demo_a VALUES (1, 'Альфа'), (2, 'Бета'), (3, 'Гамма');
INSERT INTO join_demo_b VALUES (2, 'Икс'), (3, 'Игрек'), (4, 'Зет')

Исходные данные в двух таблицах выглядят так:

Ключ соединения — столбец id. Совпадают значения 2 и 3. Значение 1 есть только в A, значение 4 — только в B. Теперь выполним все четыре типа соединения и сравним результаты

INNER JOIN — возвращает только совпадения:

SELECT a.id, a.label, b.label
FROM join_demo_a a
INNER JOIN join_demo_b b ON a.id = b.id

Две строки. Строки с id = 1 (только в A) и id = 4 (только в B) в результат не попали

LEFT JOIN — все строки из левой таблицы, совпадения из правой:

SELECT a.id, a.label, b.label
FROM join_demo_a a
LEFT JOIN join_demo_b b ON a.id = b.id

Три строки. Для id = 1 столбец b.label содержит NULL, потому что в таблице B нет строки с таким ключом. Строка с id = 4 из таблицы B отсутствует — LEFT JOIN гарантирует сохранение только левой таблицы

RIGHT JOIN — все строки из правой таблицы, совпадения из левой:

SELECT a.id, a.label, b.id, b.label
FROM join_demo_a a
RIGHT JOIN join_demo_b b ON a.id = b.id

Три строки. Для id = 4 столбцы из таблицы A содержат NULL. Зеркальное отражение LEFT JOIN

FULL JOIN — все строки из обеих таблиц:

SELECT a.id, a.label, b.id, b.label
FROM join_demo_a a
FULL JOIN join_demo_b b ON a.id = b.id

Четыре строки — видны все записи из обеих таблиц. Там, где совпадения нет, столбцы соответствующей таблицы заполнены NULL

После экспериментов демонстрационные таблицы можно удалить

DROP TABLE join_demo_a;
DROP TABLE join_demo_b

INNER JOIN на схеме bookings. Ключевое слово INNER можно опускать: запись JOIN без уточнения означает именно INNER JOIN

Соединим рейсы с аэропортами, чтобы увидеть название аэропорта отправления вместо его трёхбуквенного кода

SELECT f.flight_no,
       f.scheduled_departure,
       a.airport_name,
       a.city
FROM flights f
JOIN airports a ON f.departure_airport = a.airport_code
ORDER BY f.scheduled_departure
LIMIT 10

Условие ON f.departure_airport = a.airport_code — ключ соединения. Для каждого рейса PostgreSQL находит строку в airports, где код совпадает, и объединяет столбцы обеих таблиц в одну результирующую строку

Другой пример — связь билетов с бронированиями, позволяющая увидеть дату бронирования и итоговую сумму рядом с именем пассажира

SELECT t.ticket_no,
       t.passenger_name,
       b.book_date,
       b.total_amount
FROM tickets t
JOIN bookings b ON t.book_ref = b.book_ref
ORDER BY b.book_date DESC
LIMIT 10

LEFT JOIN и поиск «отсутствующих» данных. Один из самых распространённых сценариев применения LEFT JOIN — поиск строк, у которых нет соответствия в другой таблице. Паттерн выглядит так: LEFT JOIN + WHERE правая_таблица.ключ IS NULL

Найдём аэропорты, из которых нет ни одного отправления

SELECT a.airport_code, a.airport_name, a.city
FROM airports a
LEFT JOIN flights f ON a.airport_code = f.departure_airport
WHERE f.flight_id IS NULL

Если запрос вернул пустой результат, это означает, что в демонстрационной базе каждый аэропорт имеет хотя бы один рейс. Сам по себе паттерн при этом остаётся важным и постоянно встречается в реальных задачах

Другой пример — подсчёт количества рейсов из каждого аэропорта с сохранением в результате всех аэропортов, даже тех, из которых рейсов нет

SELECT a.airport_code,
       a.city,
       count(f.flight_id) AS departures
FROM airports a
LEFT JOIN flights f ON a.airport_code = f.departure_airport
GROUP BY a.airport_code, a.city
ORDER BY departures
LIMIT 15

Обратите внимание на использование count(f.flight_id) вместо count(*). Функция count(столбец) считает только ненулевые значения, поэтому для аэропортов без рейсов (где f.flight_id равен NULL) счётчик вернёт 0. Если бы использовался count(*), результат был бы 1, потому что count(*) считает строки, а LEFT JOIN всё равно создаёт одну строку для каждого аэропорта

Важный нюанс: если после LEFT JOIN добавить в WHERE условие по столбцу правой таблицы (кроме IS NULL), это фактически превращает LEFT JOIN в INNER JOIN, потому что строки с NULL в правой таблице будут отфильтрованы. Рассмотрим на примере. Цель — вывести все аэропорты и, если из аэропорта есть рейсы в статусе «Cancelled», показать номер рейса

-- Ошибка: WHERE по правой таблице убивает LEFT JOIN
SELECT a.airport_code, a.city, f.flight_no
FROM airports a
LEFT JOIN flights f ON a.airport_code = f.departure_airport
WHERE f.status = 'Cancelled'
LIMIT 10

Этот запрос вернёт только аэропорты с отменёнными рейсами — аэропорты без таких рейсов исчезнут из результата, хотя написан LEFT JOIN. Причина: для строк без совпадения f.status содержит NULL, а условие NULL = 'Cancelled' вычисляется в UNKNOWN и строка отбрасывается

Правильный способ — перенести условие из WHERE в ON:

-- Правильно: условие в ON сохраняет поведение LEFT JOIN
SELECT a.airport_code, a.city, f.flight_no
FROM airports a
LEFT JOIN flights f ON a.airport_code = f.departure_airport
                   AND f.status = 'Cancelled'
LIMIT 10

Теперь все аэропорты присутствуют в результате. Для аэропортов без отменённых рейсов столбец f.flight_no содержит NULL

RIGHT JOIN. RIGHT JOIN является зеркальным отражением LEFT JOIN. На практике он используется редко, потому что тот же результат всегда можно получить, поменяв таблицы местами и написав LEFT JOIN. Тем не менее полезно знать этот тип при чтении чужого кода

SELECT f.flight_no,
       a.airport_name
FROM flights f
RIGHT JOIN airports a ON f.arrival_airport = a.airport_code
WHERE f.flight_id IS NULL

Этот запрос эквивалентен LEFT JOIN с аэропортами в роли левой таблицы

FULL JOIN. Полное соединение возвращает все строки из обеих таблиц и полезно, когда нужно сравнить два множества и увидеть, что присутствует в одном, но отсутствует в другом

Сравним множества аэропортов вылета и аэропортов прибытия — есть ли аэропорты, которые используются только в одном направлении

SELECT dep.airport AS dep_only,
       arr.airport AS arr_only
FROM
    (SELECT DISTINCT departure_airport AS airport FROM flights) dep
FULL JOIN
    (SELECT DISTINCT arrival_airport AS airport FROM flights) arr
    ON dep.airport = arr.airport
WHERE dep.airport IS NULL
   OR arr.airport IS NULL

Если результат пуст, значит, каждый аэропорт используется и для вылета, и для прибытия

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

SELECT a.model, fc.fare_conditions
FROM aircrafts a
CROSS JOIN (SELECT DISTINCT fare_conditions FROM seats) fc
ORDER BY a.model, fc.fare_conditions

Все комбинации моделей самолётов и классов обслуживания

Старый синтаксис соединений и ключевое слово USING. До появления стандарта SQL-92 соединения записывались без ключевого слова JOIN: таблицы перечислялись через запятую в FROM, а условие связи помещалось в WHERE

SELECT f.flight_no, a.city
FROM flights f, airports a
WHERE f.departure_airport = a.airport_code
ORDER BY f.scheduled_departure
LIMIT 5

Этот запрос эквивалентен INNER JOIN, но менее нагляден: условие соединения смешивается с условиями фильтрации, и при сложных запросах с несколькими таблицами становится трудно отличить одно от другого. Если же в такой записи забыть условие WHERE, результатом будет декартово произведение — потенциально миллионы строк. Современный синтаксис с явным JOIN ... ON предпочтительнее, потому что отделяет логику связи от логики фильтрации

Когда столбцы, по которым выполняется соединение, называются одинаково в обеих таблицах, вместо ON можно использовать более короткую запись USING

SELECT tf.ticket_no, tf.flight_id, tf.amount,
       bp.seat_no, bp.boarding_no
FROM ticket_flights tf
JOIN boarding_passes bp USING (ticket_no, flight_id)
LIMIT 10

Конструкция USING (ticket_no, flight_id) эквивалентна ON tf.ticket_no = bp.ticket_no AND tf.flight_id = bp.flight_id. Запись короче, но работает только при совпадении имён столбцов. Кроме того, столбцы из USING появляются в результате один раз (без префикса таблицы), тогда как при ON оба столбца присутствуют отдельно

Теория и практика на sql-ex.ru. Разделы: Декартово произведение и эквисоединения, Операции соединения. JOIN. Рекомендуемые упражнения: 6, 9, 13, 16, 19, 21, 34, 50, 55

Дополнительно. SQLZoo — The JOIN operation | SQLZoo — Using NULL / Outer joins | pgexercises.com — Joins

Самосоединение и цепочки JOIN. Таблицу можно соединить саму с собой (self-join). Это необходимо, когда разные столбцы одной строки ссылаются на одну и ту же справочную таблицу. В схеме bookings ярчайший пример — таблица flights, у которой и departure_airport, и arrival_airport ссылаются на airports

SELECT f.flight_no,
       dep.city AS departure_city,
       arr.city AS arrival_city,
       f.scheduled_departure
FROM flights f
JOIN airports dep ON f.departure_airport = dep.airport_code
JOIN airports arr ON f.arrival_airport   = arr.airport_code
ORDER BY f.scheduled_departure
LIMIT 10

Здесь таблица airports участвует в запросе дважды, с разными псевдонимами (dep и arr). PostgreSQL обращается к одной и той же таблице два раза с разными условиями соединения

Цепочка из нескольких JOIN позволяет собрать данные, распределённые по всей схеме. Следующий запрос проходит по цепочке bookings → tickets → ticket_flights → flights → airports и формирует полную картину перелёта

SELECT b.book_ref,
       t.passenger_name,
       f.flight_no,
       dep.city AS departure_city,
       arr.city AS arrival_city,
       f.scheduled_departure,
       tf.fare_conditions,
       tf.amount
FROM bookings b
JOIN tickets t        ON b.book_ref   = t.book_ref
JOIN ticket_flights tf ON t.ticket_no  = tf.ticket_no
JOIN flights f        ON tf.flight_id  = f.flight_id
JOIN airports dep     ON f.departure_airport = dep.airport_code
JOIN airports arr     ON f.arrival_airport   = arr.airport_code
ORDER BY b.book_date DESC
LIMIT 15

Этот запрос затрагивает пять таблиц и содержит шесть соединений (таблица airports используется дважды). Порядок перечисления JOIN в тексте запроса не влияет на результат, потому что оптимизатор PostgreSQL самостоятельно выбирает оптимальный порядок выполнения. Для читаемости принято перечислять таблицы в логическом порядке — от «корня» связи к «листьям»

Если забыть указать условие ON в одном из соединений, PostgreSQL сообщит об ошибке синтаксиса. Однако если вместо JOIN ... ON использовать перечисление через запятую (FROM a, b, c) и забыть условие WHERE, произойдёт неявное декартово произведение, которое может создать результат из миллионов строк

Задание

1. Используя LEFT JOIN, найдите модели самолётов (aircrafts), на которые нет ни одного рейса в таблице flights
2. Постройте цепочку JOIN от boarding_passes до airports, чтобы вывести номер посадочного талона, место, город вылета и город прибытия

Операции над множествами: UNION, INTERSECT, EXCEPT

Если JOIN комбинирует таблицы горизонтально, добавляя столбцы, то операции над множествами комбинируют результаты вертикально, объединяя строки из нескольких запросов

Три правила, которые необходимо соблюдать:

• Все запросы в составе операции должны возвращать одинаковое число столбцов
• Типы данных соответствующих столбцов должны быть совместимы
• Имена столбцов в результирующем наборе берутся из первого запроса

Чтобы увидеть поведение каждой операции наглядно, представим два небольших набора данных — аэропорты вылета и аэропорты прибытия для трёх рейсов:

Множество A: {SVO, DME}. Множество B: {DME, LED, SVO}

• A UNION B → {SVO, DME, LED} — все уникальные значения из обоих множеств
• A UNION ALL B → {SVO, DME, SVO, DME, LED, SVO} — все строки без удаления дубликатов (6 строк)
• A INTERSECT B → {SVO, DME} — значения, присутствующие в обоих множествах
• A EXCEPT B → {} (пустой результат) — в A нет значений, которых бы не было в B
• B EXCEPT A → {LED} — LED есть в B, но отсутствует в A

UNION и UNION ALL. Оператор UNION объединяет результаты двух запросов и удаляет дублирующиеся строки. UNION ALL делает то же самое, но сохраняет все строки без удаления дубликатов

SELECT departure_airport AS airport_code FROM flights
UNION
SELECT arrival_airport FROM flights

Все аэропорты, упомянутые в таблице рейсов — как в качестве пункта отправления, так и в качестве пункта прибытия. Дубликаты удалены

SELECT departure_airport AS airport_code FROM flights
UNION ALL
SELECT arrival_airport FROM flights

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

Сравните количество строк в обоих результатах. Разница покажет, сколько дубликатов было удалено оператором UNION. Поскольку UNION для удаления дубликатов выполняет внутреннюю сортировку, на больших объёмах данных UNION ALL работает заметно быстрее. Если дубликаты заведомо невозможны или допустимы, предпочтительнее использовать UNION ALL

INTERSECT. Оператор INTERSECT возвращает строки, присутствующие в результатах обоих запросов. Это аналог пересечения множеств (A ∩ B)

SELECT departure_airport FROM flights
INTERSECT
SELECT arrival_airport FROM flights

Аэропорты, которые являются одновременно и пунктами вылета, и пунктами прибытия. Тот же результат можно получить через INNER JOIN или подзапрос с IN, но INTERSECT часто выглядит нагляднее

EXCEPT. Оператор EXCEPT возвращает строки, которые есть в первом запросе, но отсутствуют во втором. Это аналог разности множеств (A \ B). Порядок запросов имеет значение — если их поменять местами, результат изменится

SELECT departure_airport FROM flights
EXCEPT
SELECT arrival_airport FROM flights

Аэропорты, из которых есть вылеты, но в которые не прилетает ни один рейс

SELECT arrival_airport FROM flights
EXCEPT
SELECT departure_airport FROM flights

Обратная разность: аэропорты прибытия, которые не являются аэропортами вылета

Между операциями над множествами и другими конструкциями SQL существует прямая взаимосвязь. Покажем её на конкретном примере — запрос «аэропорты вылета, которые не являются аэропортами прибытия» можно записать тремя способами, дающими одинаковый результат

-- Через EXCEPT
SELECT departure_airport FROM flights
EXCEPT
SELECT arrival_airport FROM flights

-- Через NOT EXISTS
SELECT DISTINCT f1.departure_airport
FROM flights f1
WHERE NOT EXISTS (
    SELECT 1
    FROM flights f2
    WHERE f2.arrival_airport = f1.departure_airport
)

-- Через LEFT JOIN
SELECT DISTINCT f1.departure_airport
FROM flights f1
LEFT JOIN (SELECT DISTINCT arrival_airport FROM flights) arr
    ON f1.departure_airport = arr.arrival_airport
WHERE arr.arrival_airport IS NULL

Все три формы эквивалентны по результату. EXCEPT наиболее компактен и нагляден, NOT EXISTS гибче (позволяет добавить дополнительные условия), LEFT JOIN может быть удобен, если результат нужно тут же соединить с другими таблицами. Оптимизатор PostgreSQL во многих случаях приводит разные формулировки к одному плану выполнения, но это не гарантировано

Теория и практика на sql-ex.ru. Разделы: Реализация традиционных операций над множествами. Рекомендуемые упражнения: 7, 29, 35, 36, 41, 45, 48, 49

Задание

1. Получите список уникальных городов, которые являются либо пунктом вылета, либо пунктом прибытия (потребуется UNION + JOIN с airports)

Подзапросы

Подзапрос — это оператор SELECT, вложенный внутрь другого оператора SELECT. Подзапросы позволяют строить сложные условия фильтрации, вычислять производные данные и избегать создания промежуточных таблиц. Они могут располагаться в предложениях WHERE, FROM, HAVING и даже в списке столбцов SELECT

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

Скалярные подзапросы. Скалярный подзапрос возвращает ровно одно значение (одну строку и один столбец). Его можно использовать везде, где синтаксически ожидается одиночное значение — в WHERE, SELECT или даже в HAVING

SELECT model, range
FROM aircrafts
WHERE range = (SELECT max(range) FROM aircrafts)

Самолёт с максимальной дальностью полёта. Чтобы понять, как PostgreSQL выполняет этот запрос, полезно мысленно разделить его на два шага

Шаг 1 — вычисляется внутренний запрос:

SELECT max(range) FROM aircrafts
-- Результат: 11100

Шаг 2 — результат подставляется во внешний запрос, который фактически превращается в:

SELECT model, range FROM aircrafts WHERE range = 11100

Это некоррелированный подзапрос — он не зависит от строк внешнего запроса и вычисляется ровно один раз

Скалярный подзапрос можно использовать и в списке столбцов SELECT:

SELECT flight_no,
       scheduled_departure,
       (SELECT city FROM airports WHERE airport_code = f.departure_airport) AS dep_city
FROM flights f
ORDER BY scheduled_departure
LIMIT 10

Здесь для каждой строки из flights выполняется отдельный подзапрос, возвращающий название города. Этот подзапрос является коррелированным, потому что он ссылается на f.departure_airport из внешнего запроса. На практике такой подход менее эффективен, чем JOIN, но иногда удобен для формирования вычисляемых столбцов

Если скалярный подзапрос возвращает более одной строки, PostgreSQL выдаст ошибку. Если подзапрос не возвращает ни одной строки, результатом будет NULL

Предикат IN с подзапросом. Конструкция IN проверяет, входит ли значение в множество, возвращённое вложенным запросом

SELECT airport_name, city
FROM airports
WHERE airport_code IN (
    SELECT DISTINCT departure_airport
    FROM flights
    WHERE status = 'Cancelled'
)

Аэропорты, из которых были отменённые рейсы

NOT IN возвращает строки, значения которых не входят в результат подзапроса

SELECT model
FROM aircrafts
WHERE aircraft_code NOT IN (
    SELECT DISTINCT aircraft_code
    FROM flights
)

Модели самолётов, на которые не назначен ни один рейс

Прим. Конструкция NOT IN содержит важную ловушку при работе с NULL. Если подзапрос возвращает хотя бы одно значение NULL, весь NOT IN гарантированно вернёт пустой результат. Причина в том, что выражение x NOT IN (1, 2, NULL) преобразуется в x <> 1 AND x <> 2 AND x <> NULL, а сравнение x <> NULL всегда даёт NULL (не TRUE и не FALSE), что «отравляет» всю цепочку AND. В таких случаях безопаснее использовать NOT EXISTS

Предикат EXISTS и NOT EXISTS. Предикат EXISTS проверяет, вернул ли подзапрос хотя бы одну строку. Подзапрос внутри EXISTS всегда является коррелированным: он ссылается на столбцы внешнего запроса и выполняется для каждой строки внешней таблицы

SELECT a.airport_code, a.airport_name
FROM airports a
WHERE NOT EXISTS (
    SELECT 1
    FROM flights f
    WHERE f.departure_airport = a.airport_code
)

Аэропорты, из которых нет ни одного рейса. Результат эквивалентен примеру с LEFT JOIN ... WHERE IS NULL, рассмотренному в разделе о соединениях. Оптимизатор PostgreSQL часто приводит оба варианта к одному и тому же плану выполнения

Конструкция SELECT 1 внутри EXISTS — условность. PostgreSQL не обращает внимания на то, что именно написано в SELECT коррелированного подзапроса внутри EXISTS, потому что проверяется только наличие или отсутствие строк, а не их содержимое

SELECT DISTINCT a.city
FROM airports a
WHERE EXISTS (
    SELECT 1
    FROM flights f
    WHERE f.departure_airport = a.airport_code
      AND f.status = 'Delayed'
)

Города, из которых хотя бы один рейс имел статус «задержан»

Ключевые слова ANY/ALL. Конструкции ANY (синоним SOME) и ALL используются совместно с операторами сравнения и подзапросами. ANY возвращает TRUE, если хотя бы одно значение из подзапроса удовлетворяет условию сравнения. ALL возвращает TRUE, только если все значения из подзапроса удовлетворяют условию

SELECT model, range
FROM aircrafts
WHERE range > ALL (
    SELECT range
    FROM aircrafts
    WHERE model LIKE 'Airbus%'
)

Самолёты, дальность полёта которых превышает дальность любого из самолётов Airbus

SELECT model, range
FROM aircrafts
WHERE range > ANY (
    SELECT range
    FROM aircrafts
    WHERE model LIKE 'Boeing%'
)

Самолёты, дальность полёта которых превышает дальность хотя бы одного Boeing

Связь между этими конструкциями и уже знакомыми операторами: = ANY(...) эквивалентен IN(...), а <> ALL(...) эквивалентен NOT IN(...)

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

SELECT route.departure_city,
       route.arrival_city,
       route.flight_count
FROM (
    SELECT dep.city AS departure_city,
           arr.city AS arrival_city,
           count(*) AS flight_count
    FROM flights f
    JOIN airports dep ON f.departure_airport = dep.airport_code
    JOIN airports arr ON f.arrival_airport   = arr.airport_code
    GROUP BY dep.city, arr.city
) AS route
WHERE route.flight_count > 50
ORDER BY route.flight_count DESC

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

Когда использовать подзапрос, а когда JOIN. Часто одну и ту же задачу можно решить через подзапрос, через JOIN или через операцию над множествами. Общих рекомендаций нет, но есть ориентиры:

• Если нужны столбцы из обеих таблиц, JOIN как правило удобнее и нагляднее
• Если нужно проверить наличие или отсутствие связанных строк, EXISTS / NOT EXISTS часто выразительнее
• Если нужно сравнить значение с агрегатом по другой таблице, скалярный подзапрос — естественный выбор
• Если запрос содержит несколько этапов обработки или одна и та же промежуточная выборка нужна в нескольких местах, CTE делает структуру прозрачной
• Оптимизатор PostgreSQL во многих случаях приводит разные формулировки к одному и тому же плану выполнения

Теория и практика на sql-ex.ru. Разделы: Предикат EXISTS, SOME/ANY и ALL, Подзапросы в WHERE, Подзапросы в FROM и SELECT. Рекомендуемые упражнения: 7, 17, 18, 24, 25, 26, 27, 28, 29, 30, 37, 39, 45, 46, 48, 49, 56, 57

Дополнительно. SQLZoo — SELECT within SELECT | pgexercises.com — Basic queries

Задание

1. Выведите имена пассажиров, у которых есть хотя бы один перелёт бизнес-классом (fare_conditions = 'Business'), используя EXISTS
2. Напишите один и тот же запрос двумя способами — через IN и через EXISTS — и сравните результаты. Убедитесь, что они совпадают

Для самых стойких: общие табличные выражения (CTE)

По мере роста сложности запросов производные таблицы в FROM становятся трудночитаемыми: вложенный SELECT внутри FROM внутри внешнего SELECT создаёт глубокую структуру, в которой легко потерять логику. Общее табличное выражение (Common Table Expression, CTE) решает эту проблему иначе: промежуточный результат получает имя, определяется перед основным запросом через ключевое слово WITH и затем используется как обычная таблица

Перепишем пример с производной таблицей из предыдущего раздела через CTE

WITH route_stats AS (
    SELECT dep.city AS departure_city,
           arr.city AS arrival_city,
           count(*) AS flight_count
    FROM flights f
    JOIN airports dep ON f.departure_airport = dep.airport_code
    JOIN airports arr ON f.arrival_airport   = arr.airport_code
    GROUP BY dep.city, arr.city
)
SELECT departure_city,
       arrival_city,
       flight_count
FROM route_stats
WHERE flight_count > 50
ORDER BY flight_count DESC

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

Повторное использование. Главное преимущество CTE перед производной таблицей — возможность сослаться на один и тот же именованный результат несколько раз. С производной таблицей пришлось бы дублировать подзапрос

Допустим, нужно для каждого маршрута сравнить количество рейсов с общим средним по всей таблице

WITH route_stats AS (
    SELECT departure_airport,
           arrival_airport,
           count(*) AS flight_count
    FROM flights
    GROUP BY departure_airport, arrival_airport
)
SELECT rs.departure_airport,
       rs.arrival_airport,
       rs.flight_count,
       round(avg_all.avg_cnt, 1) AS avg_across_routes,
       CASE
           WHEN rs.flight_count > avg_all.avg_cnt THEN 'выше среднего'
           ELSE 'ниже среднего'
       END AS comparison
FROM route_stats rs
CROSS JOIN (
    SELECT avg(flight_count) AS avg_cnt
    FROM route_stats
) avg_all
ORDER BY rs.flight_count DESC
LIMIT 15

CTE route_stats вычисляется один раз, но используется дважды: в основном запросе и в подзапросе для расчёта среднего. Без CTE потребовалось бы либо продублировать GROUP BY-запрос, либо строить многоуровневую вложенность

Цепочка CTE. В одном WITH можно определить несколько CTE через запятую. Каждое последующее может ссылаться на предыдущие, что позволяет строить пошаговые конвейеры обработки

WITH departure_counts AS (
    SELECT departure_airport, count(*) AS dep_cnt
    FROM flights
    GROUP BY departure_airport
),
arrival_counts AS (
    SELECT arrival_airport, count(*) AS arr_cnt
    FROM flights
    GROUP BY arrival_airport
)
SELECT a.airport_name,
       a.city,
       COALESCE(d.dep_cnt, 0) AS departures,
       COALESCE(r.arr_cnt, 0) AS arrivals,
       COALESCE(d.dep_cnt, 0) + COALESCE(r.arr_cnt, 0) AS total_movements
FROM airports a
LEFT JOIN departure_counts d ON a.airport_code = d.departure_airport
LEFT JOIN arrival_counts r   ON a.airport_code = r.arrival_airport
ORDER BY total_movements DESC
LIMIT 10

Два CTE — статистика вылетов и статистика прилётов — а затем основной запрос, объединяющий их с таблицей аэропортов

Рекурсивные CTE. Конструкция WITH RECURSIVE позволяет CTE ссылаться на самого себя. Рекурсивные CTE применяются для обхода иерархических (древовидных) структур: организационные деревья, категории товаров, маршруты с пересадками

WITH RECURSIVE reachable AS (
    -- Базовый случай: прямые рейсы из Москвы (SVO)
    SELECT departure_airport,
           arrival_airport,
           ARRAY[departure_airport, arrival_airport] AS route,
           1 AS legs
    FROM (SELECT DISTINCT departure_airport, arrival_airport FROM flights) direct
    WHERE departure_airport = 'SVO'

    UNION ALL

    -- Рекурсивный шаг: добавляем ещё один перелёт
    SELECT r.departure_airport,
           f.arrival_airport,
           r.route || f.arrival_airport,
           r.legs + 1
    FROM reachable r
    JOIN (SELECT DISTINCT departure_airport, arrival_airport FROM flights) f
        ON r.arrival_airport = f.departure_airport
    WHERE r.legs < 2                          -- ограничиваем глубину
      AND NOT f.arrival_airport = ANY(r.route) -- избегаем циклов
)
SELECT departure_airport,
       arrival_airport,
       route,
       legs
FROM reachable
WHERE arrival_airport = 'KJA'
ORDER BY legs

Запрос ищет маршруты из Шереметьево (SVO) в Красноярск (KJA) с не более чем одной пересадкой. Рекурсивный CTE состоит из двух частей: базовый случай (прямые рейсы из SVO) и рекурсивный шаг (к каждому достижимому аэропорту добавляется ещё один перелёт). Условие legs < 2 ограничивает глубину рекурсии, а проверка NOT arrival_airport = ANY(route) предотвращает зацикливание

Особенность PostgreSQL. CTE в PostgreSQL до версии 12 всегда материализовались — вычислялись целиком до начала работы основного запроса, и оптимизатор не мог «протолкнуть» фильтры внутрь CTE. Начиная с PostgreSQL 12, для нерекурсивных CTE, на которые ссылаются один раз, оптимизатор может выполнить встраивание автоматически. При необходимости поведение задаётся явно: WITH stats AS MATERIALIZED (...) — принудительная материализация, WITH stats AS NOT MATERIALIZED (...) — принудительное встраивание

Задание

1. Перепишите пример с производной таблицей (популярные маршруты) из раздела «Подзапросы» через CTE и сравните читаемость обоих вариантов
2. Попробуйте выполнить рекурсивный запрос из примера выше, подставив коды аэропортов вашего региона

Итоговое практическое задание

Для закрепления материала необходимо решить набор упражнений на тренажёре sql-ex.ru. Упражнения сгруппированы по темам работы. Каждое упражнение проверяется тренажёром автоматически

Часть 1. SELECT, WHERE, предикаты (8 упражнений)

Упражнения: 1, 2, 3, 4, 5, 8, 23, 42

Покрывают: базовый SELECT, фильтрацию WHERE, предикаты сравнения, BETWEEN, IN, DISTINCT, ORDER BY

Часть 2. Агрегатные функции и GROUP BY (8 упражнений)

Упражнения: 10, 11, 12, 14, 15, 20, 22, 33

Покрывают: COUNT, SUM, AVG, MIN, MAX, группировку, HAVING

Часть 3. Соединения таблиц (8 упражнений)

Упражнения: 6, 9, 13, 16, 19, 21, 34, 50

Покрывают: INNER JOIN, LEFT JOIN, соединение нескольких таблиц, самосоединение

Часть 4. Подзапросы и операции над множествами (8 упражнений)

Упражнения: 7, 17, 18, 24, 29, 35, 36, 45

Покрывают: IN с подзапросом, EXISTS, ANY/ALL, UNION, INTERSECT, EXCEPT

В сумме — 32 упражнения. Все перечисленные задачи решаются средствами, рассмотренными в этой работе

Прим. На sql-ex.ru используются собственные учебные базы данных (корабли, компьютерная фирма, переработка вторсырья), а не схема bookings. Описание баз доступно в разделе Приложения. Работа с разными схемами — полезный навык, потому что на практике приходится писать запросы к незнакомым базам, ориентируясь по структуре таблиц

Дополнительная практика на других платформах (по желанию)

• pgexercises.com — разделы Basic, Joins, Aggregation (бесплатно, PostgreSQL)
• SQLZoo — уроки SELECT basics, SELECT from WORLD, JOIN, SUM and COUNT, SELECT within SELECT
• SQLBolt — уроки 1–12

Требования к отчету

Отчет оформляется одним файлом (.md, .pdf или .docx)

1. Скриншот профиля sql-ex.ru, подтверждающий решение упражнений из частей 1–4 (на странице профиля видны номера решённых задач)

2. Три запроса с пояснениями. Выберите три упражнения, которые показались наиболее интересными или сложными. Для каждого приведите текст запроса и 2–3 предложения: в чём заключалась задача и какой подход вы использовали для решения

3. Выводы. 3–5 предложений: какие конструкции оказались новыми, в чём разница между JOIN и подзапросами на практике, какой подход к формулировке запроса показался наиболее удобным