Часто запрос отвечает на вопрос через другой вопрос: «напитки дороже средней цены» (а какая средняя?), «клиенты, у которых есть заказы» (а есть ли?), «напитки, которых нет ни в одной акции». Внутренний вопрос — это подзапрос: запрос внутри запроса. Он бывает трёх форм, и выбор между двумя из них — IN и EXISTS — это не вкусовщина: на данных с NULL они дают разные ответы, и NOT IN способен молча вернуть «ничего».

Мы уже видели эту ловушку в 03-06 как урок о трёхзначной логике NULL. Здесь смотрим на неё с другой стороны — как на главный довод выбирать EXISTS для «нет среди».

Три формы подзапроса

Scalar — подзапрос возвращает одно значение и подставляется как обычное число/строка:

WHERE base_price > (SELECT avg(base_price) FROM drinks)

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

IN — проверяет, что значение есть в наборе из подзапроса: id IN (SELECT drink_id FROM order_items) — «напиток, чей id встречается среди заказанных».

EXISTS — коррелированный подзапрос: для каждой внешней строки спрашивает «есть ли хотя бы одна совпадающая строка внутри». EXISTS не интересуют значения — только факт наличия, поэтому внутри пишут SELECT 1 и он останавливается на первом совпадении.

IN против EXISTS: почему это важно

Для «есть среди» IN и EXISTS обычно дают одинаковый результат, и планировщик часто превращает один в другой. Разница вылезает на отрицании (NOT IN vs NOT EXISTS), когда подзапрос может вернуть NULL.

x NOT IN (a, b, NULL) Postgres раскрывает как NOT (x=a OR x=b OR x=NULL). Член x=NULL — это всегда NULL (не false!). Если x не равен ни a, ни b, получается NOT (false OR false OR NULL) = NOT (NULL) = NULL — а строка с условием NULL фильтр не проходит. Достаточно одного NULL в наборе, чтобы NOT IN вернул пусто для всех:

ищем напитки НЕ на акции; акции (featured_drink_id) = {1, NULL}
 
  d.id NOT IN (1, NULL)
       = NOT ( d.id = 1  OR  d.id = NULL )
                                └── сравнение с NULL → NULL, не false
 
  напиток #4 (в акциях его нет):
       NOT ( false OR NULL ) = NOT (NULL) = NULL   → строка не проходит фильтр
 
  один NULL в наборе → NOT IN отсеивает ВСЕХ → ответ 0 (хотя их 4)

NOT EXISTS так не ломается: он спрашивает «нет ли совпадающей строки», а строка подзапроса с NULL ни с чем не совпадает (NULL не равен ничему) — значит, никого лишнего не исключает. Поэтому правило простое: для «нет среди» — NOT EXISTS (или NOT IN с гарантированно не-NULL подзапросом).

Что показывает наш код

Подзапросы по базовым таблицам:

-- AbovePriceAvg:           WHERE base_price > (SELECT avg(base_price) FROM drinks)
-- DrinksOrdered:           WHERE id IN (SELECT drink_id FROM order_items)
-- CountCustomersWithOrders WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id::text)

И ловушка на лабораторной promo, где featured_drink_id может быть NULL (акция «на всё меню»):

-- CountNotFeaturedNotIn:     WHERE id NOT IN (SELECT featured_drink_id FROM promo)   -- → 0 (ловушка)
-- CountNotFeaturedNotExists: WHERE NOT EXISTS (SELECT 1 FROM promo p WHERE p.featured_drink_id = d.id)  -- → 4

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew:

docker compose up -d
make lecture L=04-querying-across-tables/04-05-subqueries-exists-vs-in T=db-reset
make lecture L=04-querying-across-tables/04-05-subqueries-exists-vs-in

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

1) Scalar-подзапрос — напитки дороже средней цены (avg=4.00):
   #2 Капучино     4.50
   #3 Латте        4.80
   #4 Колд брю     5.20
 
2) IN-подзапрос — напитки, которые хоть раз заказывали (4): Эспрессо, Капучино, Латте, Колд брю
   → зелёного чая нет: его не заказывали ни разу.
 
3) EXISTS-подзапрос — клиентов хотя бы с одним заказом: 2 (Карина без заказов не в счёт).
 
4) «Сколько напитков НЕ на акции?» — акции = {эспрессо #1, всё меню (NULL)}:
   NOT IN (...)      → 0   ← ловушка: NULL в списке обнулил ответ
   NOT EXISTS (...)  → 4   ← правильно (5 напитков минус эспрессо #1)

Первые три формы отработали как ожидалось. А пункт 4 — главный: NOT IN со списком {1, NULL} вернул 0 (хотя «не на акции» четыре напитка), NOT EXISTS — честные 4. Один NULL в наборе обнулил весь NOT IN.

Заборчик

Что мы упростили.

• Ловушка NOT IN + NULL — не редкость: подзапрос по nullable-колонке (а их в схемах много) рано или поздно вернёт NULL, и NOT IN молча соврёт. Поэтому в проде «нет среди» пишут через NOT EXISTS либо явно отсекают NULL (... WHERE featured_drink_id IS NOT NULL).
• Производительность: на наших данных разницы нет, но на больших таблицах EXISTS/NOT EXISTS обычно дружелюбнее к индексам (останавливается на первом совпадении), а IN с огромным списком значений из приложения лучше заменить на = ANY($1::bigint[]) — это отдельный разговор в 10-03.
• Scalar-подзапрос обязан вернуть ровно одно значение: вернёт несколько — ошибка на проде, а не тихий баг (тут хотя бы шумно).

Что забрать с собой

• Scalar-подзапрос подставляется как одно значение; вернёт больше одной строки — ошибка времени выполнения.
• IN (подзапрос) — «значение есть в наборе»; EXISTS (подзапрос) — «есть хотя бы одна совпадающая строка» (значения не важны, пишут SELECT 1).
• NOT IN со списком, где затесался NULL, возвращает пусто для всех — NOT (… OR NULL) сворачивается в NULL.
• Для «нет среди» бери NOT EXISTS (или NOT IN с гарантированно не-NULL подзапросом).
• EXISTS/NOT EXISTS обычно дружелюбнее к индексам; гигантский IN-список из приложения — кандидат на = ANY($1::тип[]) (10-03).

Подзапросы решают задачу «вопрос внутри вопроса», но стоит их вложить два-три уровня — и запрос перестаёт читаться. Их можно вынести в именованные шаги через WITH и собрать из них конвейер сверху вниз — куда понятнее вложенности. Дальше — 04-06 «CTE и материализация»: соберём читаемый конвейер из шагов и разберём, когда Postgres «материализует» CTE в промежуточную таблицу, а когда встраивает его в основной запрос.