Управляющая «Пенки» спрашивает: какие напитки у нас дороже среднего? Вопрос простой, но в один шаг на него не ответить. Сначала нужно узнать среднюю цену, потом сравнить с ней каждый напиток. В прошлом уроке мы склеивали таблицы бок о бок. Сегодня вкладываем один запрос внутрь другого.
Вопрос внутри вопроса
Представь, что ты спрашиваешь бариста: «Сделай мне то же, что заказывал Дима в прошлый раз». Бариста сначала заглядывает в журнал и находит Димин заказ, а потом варит по нему кофе. Твой вопрос содержит другой вопрос, и ответ на внутренний становится частью внешнего.
В SQL так же. Подзапрос это SELECT в круглых скобках, который стоит внутри другого запроса. SQLite выполняет его и подставляет ответ на место скобок.
Подзапрос, который возвращает одно значение
Средняя цена напитка считается функцией avg. Подробно агрегатные функции будут в уроке 17, а пока достаточно знать, что avg возвращает одно число:
sqlite> SELECT avg(price) FROM drinks;
╭────────────╮
│ avg(price) │
╞════════════╡
│ 177.5 │
╰────────────╯Можно было бы переписать 177.5 руками во второй запрос. Но завтра цены поменяются, и число устареет. Вместо числа ставим сам запрос:
sqlite> SELECT name, price
...> FROM drinks
...> WHERE price > (SELECT avg(price) FROM drinks);
╭──────────┬───────╮
│ name │ price │
╞══════════╪═══════╡
│ Капучино │ 190 │
│ Латте │ 210 │
│ Раф │ 240 │
│ Какао │ 180 │
│ Тархун │ 200 │
╰──────────┴───────╯Такой подзапрос называют скалярным: он возвращает одно значение, и его можно поставить туда, где SQL ждёт число. Матча-латте без цены в среднее не вошла и в ответ не попала: про то, как агрегаты обходят NULL, поговорим в уроке 17.
IN: значение из списка
Подзапрос может вернуть не одно значение, а целый столбец. Тогда его используют с IN, который ты видел в уроке про WHERE. Какие постоянные гости хоть раз платили наличными?
sqlite> SELECT name
...> FROM customers
...> WHERE id IN (SELECT customer_id FROM orders WHERE paid = 'наличные');
╭──────╮
│ name │
╞══════╡
│ Пётр │
╰──────╯Внутренний запрос вернул номера гостей из заказов за наличные, внешний оставил гостей с такими номерами. Тот же ответ дал бы JOIN, но с подзапросом мысль читается прямо: «гости, чей номер есть среди…».
Ловушка NOT IN
Перевернём вопрос: какие гости ещё ни разу не заказывали? Кажется, достаточно заменить IN на NOT IN:
sqlite> SELECT name FROM customers
...> WHERE id NOT IN (SELECT customer_id FROM orders);Пусто. А мы знаем, что Света и Кирилл ничего не заказывали. Дело в заказах гостей без карты: у них customer_id равен NULL, и этот NULL попал в список. Проверим на маленьком примере:
sqlite> SELECT 5 NOT IN (1, 2, NULL) AS answer;
╭────────╮
│ answer │
╞════════╡
│ NULL │
╰────────╯NOT IN проверяет, что значение не равно каждому элементу списка. Пятёрка не равна единице, не равна двойке, а равна ли она NULL, неизвестно. Итог тоже «неизвестно», и WHERE такую строку отбрасывает. Это та же трёхзначная логика, что и в уроке про NULL. Один пустой номер в подзапросе обнуляет весь ответ, и никакой ошибки ты не увидишь.
Лечится явным фильтром внутри подзапроса:
sqlite> SELECT name FROM customers
...> WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);
╭────────╮
│ name │
╞════════╡
│ Света │
│ Кирилл │
╰────────╯EXISTS: есть ли хоть одна строка
Надёжнее для таких вопросов EXISTS. Он не сравнивает значения, а проверяет, вернул ли подзапрос хоть одну строку:
sqlite> SELECT name FROM customers c
...> WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
╭────────╮
│ name │
╞════════╡
│ Света │
│ Кирилл │
╰────────╯Обрати внимание на o.customer_id = c.id: подзапрос ссылается на c, гостя из внешнего запроса. Для каждого гостя SQLite выполняет подзапрос заново со своим c.id. NULL в чужих заказах тут ничего не ломает: для Светы подзапрос просто не находит строк. SELECT 1 внутри означает, что столбцы нам не важны, важно только, есть ли строки.
Коррелированный подзапрос в SELECT
Подзапрос, который зависит от строки внешнего запроса, называют коррелированным. Его можно поставить и в список столбцов. Посчитаем визиты каждого гостя:
sqlite> SELECT c.name,
...> (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS visits
...> FROM customers c;
╭────────┬────────╮
│ name │ visits │
╞════════╪════════╡
│ Маша │ 3 │
│ Пётр │ 2 │
│ Лена │ 2 │
│ Дима │ 2 │
│ Света │ 0 │
│ Кирилл │ 0 │
╰────────┴────────╯count(*) считает строки, которые вернул подзапрос. Для каждого гостя он свой. Работает как вложенный цикл: снаружи перебираем гостей, внутри считаем их заказы. На тысяче гостей это быстро, на миллионе стоит подумать об индексе, но об этом в уроке про индексы.
Подзапрос вместо таблицы
Подзапрос можно поставить и в FROM. Тогда его результат становится временной таблицей, из которой выбирает внешний запрос. Найдём строки заказов, которые принесли кофейне 400 рублей и больше:
sqlite> SELECT order_id, name, cost
...> FROM (SELECT i.order_id, d.name, i.qty * d.price AS cost
(x1...> FROM order_items i JOIN drinks d ON d.id = i.drink_id)
...> WHERE cost >= 400;
╭──────────┬────────┬──────╮
│ order_id │ name │ cost │
╞══════════╪════════╪══════╡
│ 5 │ Раф │ 480 │
│ 11 │ Тархун │ 400 │
╰──────────┴────────┴──────╯Зачем так сложно? По стандарту SQL WHERE не видит псевдоним cost из того же SELECT: фильтр работает раньше, чем считаются столбцы. SQLite такое прощает, а PostgreSQL ответит ошибкой. Подзапрос делает порядок явным: сначала строит столбец cost, потом внешний запрос фильтрует уже по готовому, и запрос работает в любой базе. Обрати внимание на приглашение (x1...>: sqlite3 подсказывает, что одна скобка ещё не закрыта.
WITH: подзапрос с именем
Вложенные скобки быстро становятся нечитаемыми. WITH выносит подзапрос вперёд и даёт ему имя:
sqlite> WITH lines AS (
(x1...> SELECT i.order_id, d.name, i.qty * d.price AS cost
(x1...> FROM order_items i JOIN drinks d ON d.id = i.drink_id
(x1...> )
...> SELECT order_id, name, cost FROM lines WHERE cost >= 400;
╭──────────┬────────┬──────╮
│ order_id │ name │ cost │
╞══════════╪════════╪══════╡
│ 5 │ Раф │ 480 │
│ 11 │ Тархун │ 400 │
╰──────────┴────────┴──────╯Ответ тот же, но читается сверху вниз: сначала «строки заказа с ценой», потом «оставь дорогие». Так пишут запросы, которые разбиты на понятные шаги. Подробнее про подзапросы и WITH есть уроки про подзапросы и про CTE, а в PostgreSQL они разобраны в отдельном уроке.
Попробуй сам
В песочнице два вопроса к «Пенке»: напитки дешевле среднего и гости, которые хоть раз брали капучино.
Перепиши второй запрос через IN и сравни ответы. Потом найди сотрудников, которые не приняли ни одного заказа, через NOT EXISTS.
Проверь себя
Задание
Дальше
Подзапрос с o.customer_id = c.id связывал две разные таблицы. А если связь ведёт в ту же таблицу? У сотрудника есть руководитель, и он тоже сотрудник. Следующий урок про самосоединение.
домашка