Раздел 34 · MySQL с нуля

Подзапросы и UNION

junior~30 мин

без входаВойди, чтобы отмечать прогресс, вести заметки и решать задачи в редакторе. войти

В прошлом уроке мы сворачивали строки в итоги. Теперь вопрос посложнее: какие фильмы длиннее среднего? Чтобы ответить, сначала надо узнать среднее, а потом сравнить с ним каждый фильм. Два шага, два запроса. Сегодня учимся вкладывать один запрос в другой и склеивать ответы нескольких запросов в один.

Вопрос внутри вопроса

Ты просишь кассира: «Дай фильмы длиннее среднего». Кассир сначала пересчитает среднюю длительность по каталогу, запишет число на бумажке и только потом пойдёт по карточкам, сравнивая каждую с этой бумажкой. Бумажка и есть подзапрос: маленький запрос в скобках, чей ответ подставляется в большой.

Скалярный подзапрос: одно число

Сначала посмотрим на среднее отдельно:

SELECT AVG(duration_min) FROM films;
+-------------------+
| AVG(duration_min) |
+-------------------+
|          122.8571 |
+-------------------+
1 row in set (0.00 sec)

Теперь вставим этот запрос в WHERE вместо числа:

SELECT title, duration_min
FROM films
WHERE duration_min > (SELECT AVG(duration_min) FROM films)
ORDER BY duration_min;
+-------------------------------------------+--------------+
| title                                     | duration_min |
+-------------------------------------------+--------------+
| Москва слезам не верит                    |          150 |
| Сталкер                                   |          163 |
| Солярис                                   |          167 |
| Ирония судьбы                             |          184 |
+-------------------------------------------+--------------+
4 rows in set (0.00 sec)

Такой подзапрос называют скалярным: он возвращает одно значение. Ещё пример, вторая по величине цена сеанса. Берём максимум среди цен, которые меньше общего максимума:

SELECT MAX(price) FROM screenings WHERE price < (SELECT MAX(price) FROM screenings);
+------------+
| MAX(price) |
+------------+
|     450.00 |
+------------+
1 row in set (0.00 sec)

Если подзапрос, от которого ждут одно значение, вернул несколько строк, MySQL откажется:

SELECT title FROM films WHERE id = (SELECT film_id FROM screenings);
ERROR 1242 (21000): Subquery returns more than 1 row

Знак = сравнивает с одним значением, а сеансов семь. Когда подзапрос точно вернёт одну строку, например ищет по первичному ключу, = работает:

SELECT title FROM films WHERE id = (SELECT film_id FROM screenings WHERE id = 4);
+---------------------------+
| title                     |
+---------------------------+
| Ирония судьбы             |
+---------------------------+
1 row in set (0.00 sec)

Сеанс 4 один, и фильм у него один. Сравнить со списком умеет IN.

IN: значение из списка

Подзапрос внутри IN возвращает колонку значений. Зрители, у которых есть оплаченный билет:

SELECT name FROM viewers
WHERE id IN (SELECT viewer_id FROM tickets WHERE status = 'paid')
ORDER BY name;
+---------------------------+
| name                      |
+---------------------------+
| Анна Ковалёва             |
| Борис Орлов               |
| Вера Ильина               |
| Глеб Соколов              |
| Дарья Белова              |
+---------------------------+
5 rows in set (0.00 sec)

Анна попала в ответ один раз, хотя билетов у неё два: IN проверяет, есть ли значение в списке, и повторы ему не мешают.

NOT IN и ловушка с NULL

Обратный вопрос: кто не купил ни одного билета? Ответ должен быть «Егор Ёлкин».

SELECT name FROM viewers WHERE id NOT IN (SELECT viewer_id FROM tickets);
Empty set (0.00 sec)

Пусто. Виноваты кассовые билеты: в списке viewer_id есть NULL. Для Егора проверка звучит так: «6 не равно 1, не равно 2, …, и не равно NULL». Последнее сравнение даёт «неизвестно», как в уроке про SELECT, и всё условие становится «неизвестно». Один NULL в подзапросе обнуляет весь NOT IN.

Лечится явным фильтром:

SELECT name FROM viewers WHERE id NOT IN (SELECT viewer_id FROM tickets WHERE viewer_id IS NOT NULL);
+---------------------+
| name                |
+---------------------+
| Егор Ёлкин          |
+---------------------+
1 row in set (0.00 sec)

Работает, но об этом фильтре легко забыть. Надёжнее NOT EXISTS.

EXISTS и коррелированный подзапрос

EXISTS спрашивает одно: вернёт ли подзапрос хотя бы одну строку. Сами значения ему не важны, поэтому внутри пишут SELECT 1.

SELECT name FROM viewers v
WHERE NOT EXISTS (SELECT 1 FROM tickets t WHERE t.viewer_id = v.id);
+---------------------+
| name                |
+---------------------+
| Егор Ёлкин          |
+---------------------+
1 row in set (0.00 sec)

Посмотри на условие внутри: t.viewer_id = v.id. Подзапрос ссылается на строку внешнего запроса. Такой подзапрос называют коррелированным: для каждого зрителя он задаёт свой вопрос, «есть ли билет у этого человека». NULL здесь ничего не ломает: у кассового билета viewer_id NULL, сравнение с ним не истинно, и такой билет просто никому не засчитывается.

Ещё пример: фильмы, на которые хоть раз сдавали билет.

SELECT title FROM films f
WHERE EXISTS (
  SELECT 1 FROM screenings s JOIN tickets t ON t.screening_id = s.id
  WHERE s.film_id = f.id AND t.status = 'refunded'
);
+-------------------------------------------+
| title                                     |
+-------------------------------------------+
| Москва слезам не верит                    |
+-------------------------------------------+
1 row in set (0.00 sec)

Коррелированный подзапрос можно поставить и в SELECT, тогда он даёт значение для каждой строки. Число сеансов у фильма:

SELECT f.title, (SELECT COUNT(*) FROM screenings s WHERE s.film_id = f.id) AS shows
FROM films f
ORDER BY f.id;
+-------------------------------------------+-------+
| title                                     | shows |
+-------------------------------------------+-------+
| Брат                                      |     2 |
| Москва слезам не верит                    |     1 |
| Ирония судьбы                             |     1 |
| Сталкер                                   |     1 |
| Ёжик в тумане                             |     1 |
| Новые имена                               |     1 |
| Солярис                                   |     0 |
+-------------------------------------------+-------+
7 rows in set (0.00 sec)

Ответ тот же, что давал LEFT JOIN с GROUP BY в прошлом уроке. Часто одну задачу можно решить и соединением, и подзапросом. Как выбирать? Соединение нужно, когда в ответе нужны колонки обеих таблиц. Подзапрос с EXISTS удобнее, когда вторая таблица только отвечает на вопрос «есть или нет»: он не размножает строки, и зритель с двумя билетами не появится в ответе дважды. Оптимизатор MySQL часто превращает одно в другое сам, так что начинать стоит с той записи, которая понятнее читается. Сравнение способов найти строки без пары есть в уроке для собеседования.

Производная таблица

Подзапрос можно поставить и во FROM. Тогда его ответ работает как временная таблица, у которой обязательно должно быть имя. Её называют производной таблицей:

SELECT hall, total
FROM (
  SELECT h.name AS hall, COUNT(*) AS total
  FROM screenings s JOIN halls h ON h.id = s.hall_id
  GROUP BY h.name
) AS per_hall
ORDER BY total DESC, hall;
+----------------+-------+
| hall           | total |
+----------------+-------+
| Большой        |     3 |
| Малый          |     3 |
| VIP            |     1 |
+----------------+-------+
3 rows in set (0.00 sec)

Вложенные скобки читаются тяжело. Тот же приём удобнее записать через WITH: сначала даёшь подзапросу имя, потом пользуешься им как таблицей. Это CTE, MySQL поддерживает их с версии 8.0:

WITH sold AS (
  SELECT screening_id, COUNT(*) AS n FROM tickets GROUP BY screening_id
)
SELECT s.id, s.starts_at, COALESCE(sold.n, 0) AS sold
FROM screenings s
LEFT JOIN sold ON sold.screening_id = s.id
ORDER BY s.id;
+----+---------------------+------+
| id | starts_at           | sold |
+----+---------------------+------+
|  1 | 2026-10-23 19:00:00 |    3 |
|  2 | 2026-10-24 21:30:00 |    1 |
|  3 | 2026-10-24 18:00:00 |    3 |
|  4 | 2026-10-25 20:00:00 |    2 |
|  5 | 2026-10-25 17:00:00 |    1 |
|  6 | 2026-10-26 11:00:00 |    1 |
|  7 | 2026-10-26 22:00:00 |    0 |
+----+---------------------+------+
7 rows in set (0.00 sec)

COALESCE заменил NULL у сеанса 7 на ноль. Подробнее о CTE в уроке для собеседования, а в разделе про PostgreSQL их разбирают вместе с оконными функциями в уроке 16.

UNION: ответы стопкой

Подзапросы вкладывают запрос в запрос. UNION ставит ответы двух запросов друг под другом. Города зрителей плюс два города, где мы хотим открыться:

SELECT city FROM viewers UNION SELECT 'Москва' UNION SELECT 'Тверь' ORDER BY city;
+-------------------------------+
| city                          |
+-------------------------------+
| NULL                          |
| Казань                        |
| Москва                        |
| Санкт-Петербург               |
| Тверь                         |
+-------------------------------+
5 rows in set (0.00 sec)

Каждый SELECT в этой цепочке самостоятельный: у него свой FROM и свой WHERE, а у последних двух FROM нет вовсе, они возвращают одну строку с готовым значением. UNION убирает повторы: Москва встречается трижды, а в ответе одна. NULL от Глеба тоже остался одной строкой. Если повторы нужны, пиши UNION ALL, он ничего не сравнивает и поэтому работает быстрее:

SELECT city FROM viewers WHERE city IS NOT NULL UNION ALL SELECT 'Москва' ORDER BY city;
+-------------------------------+
| city                          |
+-------------------------------+
| Казань                        |
| Казань                        |
| Москва                        |
| Москва                        |
| Москва                        |
| Санкт-Петербург               |
+-------------------------------+
6 rows in set (0.00 sec)

Склеивать можно и разные таблицы. Имена колонок ответа берутся из первого SELECT, а ORDER BY в конце сортирует весь результат:

SELECT name AS who, 'зритель' AS kind FROM viewers WHERE city = 'Казань'
UNION
SELECT director, 'режиссёр' FROM films WHERE release_year < 1976
ORDER BY who;
+-----------------------+------------------+
| who                   | kind             |
+-----------------------+------------------+
| Борис Орлов           | зритель          |
| Егор Ёлкин            | зритель          |
| Норштейн              | режиссёр         |
| Рязанов               | режиссёр         |
| Тарковский            | режиссёр         |
+-----------------------+------------------+
5 rows in set (0.00 sec)

Повторы UNION убрал бы и здесь: будь у Тарковского два фильма до 1976 года, в списке он всё равно оказался бы один раз. Условие у склейки одно: число колонок в каждом SELECT должно совпадать.

SELECT title FROM films UNION SELECT name, city FROM viewers;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

Что запомнить

  • Скалярный подзапрос возвращает одно значение, а если строк больше одной, получишь ошибку 1242.
  • NOT IN по списку с NULL не вернёт ни одной строки: для поиска «без пары» надёжнее NOT EXISTS.
  • Коррелированный подзапрос ссылается на строку внешнего запроса и выполняется для каждой из них.
  • Производной таблице в FROM обязательно нужно имя.
  • UNION убирает повторы, UNION ALL оставляет все строки, и число колонок в частях должно совпадать.

Подзапрос и соединение часто решают одну задачу. Начинай с той записи, которую легче прочитать вслух, а когда таблицы вырастут, сравни планы обоих вариантов через EXPLAIN из урока про индексы. Порядок строк у UNION без ORDER BY тоже не гарантирован, поэтому сортировку ставь в самый конец, после последней части. Она относится ко всему объединению сразу, а не к последнему SELECT.

Попробуй сам

В песочнице база kino. Первый запрос ищет зрителей без билетов через NOT IN и падает в ловушку, второй делает то же через NOT EXISTS.

Почини первый запрос фильтром IS NOT NULL. Найди фильмы без сеансов через NOT EXISTS. Склей через UNION названия залов и названия фильмов в один список и проверь, чьё имя получила колонка.

Проверь себя

Задание

Дальше

Весь кластер мы только читали данные. Пора их менять: добавлять зрителей, поднимать цены, удалять отменённые сеансы. Следующий урок про INSERT, UPDATE и DELETE.

домашка

Домашка