Подзапросы: EXISTS, IN, ANY, ALL

JOIN - основной инструмент связи таблиц. Но есть класс вопросов, которые естественнее выражаются иначе: «найди сотрудников, чья зарплата выше средней по их отделу» или «отдели заказы без соответствующего клиента». Подзапросы позволяют строить запросы из запросов - как функции из функций. Главное: знать, когда это эффективно, а когда это ловушка.

• **Антифрод:** найти транзакции, сумма которых выше среднего по пользователю за последние 30 дней - коррелированный подзапрос или window function
• **Отчётность:** отделы без ни одного сотрудника с определённым навыком - NOT EXISTS намного безопаснее NOT IN при nullable данных
• **Data quality:** найти записи-сироты (заказы без клиента, позиции без товара) - LEFT JOIN + IS NULL или NOT EXISTS

Скалярные подзапросы

**Скалярный подзапрос** - это SELECT внутри другого выражения, который возвращает ровно одну строку и один столбец. Он может появляться везде, где допускается скалярное значение: в SELECT, WHERE, HAVING, ORDER BY.

**Важно:** если скалярный подзапрос возвращает NULL (нет строк) или более одной строки - PostgreSQL выбросит ошибку. Для безопасности используйте `LIMIT 1` или агрегатную функцию.

Табличные подзапросы в FROM

Подзапрос в предложении FROM возвращает виртуальную таблицу - **derived table** (или inline view). К ней можно обращаться как к обычной таблице: делать JOIN, GROUP BY, WHERE. Это удобный способ разбить сложный запрос на читаемые слои.

**ANY и ALL:** `> ANY(subquery)` истинно, если значение больше хотя бы одного результата подзапроса. `> ALL(subquery)` - если больше всех. `= ANY(subquery)` эквивалентен `IN (subquery)`.

Коррелированные подзапросы

**Коррелированный подзапрос** ссылается на столбцы из внешнего запроса. Он выполняется заново для *каждой строки* внешнего запроса. Это даёт гибкость, но может быть медленным - N строк во внешнем запросе = N выполнений подзапроса.

Коррелированные подзапросы незаменимы для «найти по своей группе», но планировщик PostgreSQL часто умеет их переписать в JOIN или window function. `EXPLAIN ANALYZE` покажет, произошла ли такая оптимизация.

**LATERAL:** расширение коррелированных подзапросов для FROM. `FROM employees e, LATERAL (SELECT ... WHERE dept_id = e.id LIMIT 3) top3` - позволяет коррелированный подзапрос прямо в FROM, возвращая несколько строк на каждую строку внешней таблицы.

EXISTS vs IN: когда что использовать

Вопрос «есть ли сотрудники в этом отделе» можно задать двумя способами: `IN (subquery)` и `EXISTS (subquery)`. Семантически они эквивалентны для NOT NULL данных, но работают по-разному и ведут себя по-разному при NULL.

**PostgreSQL оптимизация:** планировщик часто трансформирует `IN (subquery)` в semi-join, а `EXISTS` - тоже в semi-join. На практике в PostgreSQL 14+ разница в плане между IN и EXISTS часто нулевая. Главное правило - используйте `NOT EXISTS` вместо `NOT IN` при возможных NULL.

Производительность подзапросов

Подзапросы - не волшебство: неправильно написанный подзапрос может превратить запрос из миллисекунд в минуты. Ключевые паттерны: коррелированный подзапрос в SELECT вместо JOIN, NOT IN с большими таблицами, подзапрос без индекса на условии корреляции.

PostgreSQL планировщик умеет трансформировать многие подзапросы в JOIN автоматически. Но гарантии нет - зависит от статистики, размера таблиц, наличия индексов. `EXPLAIN ANALYZE` - единственный способ убедиться в эффективности плана.

**Полезная эвристика:** если подзапрос в WHERE возвращает большой набор данных и не является коррелированным - планировщик скорее всего уже превратил его в HashJoin. Если же подзапрос коррелированный и в SELECT - это почти наверняка N лишних запросов к таблице.

Подзапросы в PostgreSQL

• Скалярный подзапрос: ровно 1 строка, 1 столбец - допустим в SELECT/WHERE/HAVING/ORDER BY
• Derived table (FROM subquery): виртуальная таблица, обязателен алиас; основа для сложных агрегаций
• Коррелированный подзапрос: ссылается на внешнюю строку, выполняется N раз - гибко, но следите за планом
• NOT IN с NULL в подзапросе возвращает 0 строк - всегда используйте NOT EXISTS для nullable столбцов
• EXPLAIN ANALYZE - единственный способ проверить, что план действительно эффективен

Связанные темы

Подзапросы - строительный блок для CTE и оконных функций.

• CTE и WITH RECURSIVE — CTE - именованные подзапросы, более читаемые и иногда материализованные
• Оконные функции — Многие коррелированные подзапросы заменяются оконными функциями с лучшей производительностью
• EXPLAIN и планировщик — Анализ плана выполнения подзапросов - ключевой инструмент оптимизации

Вопросы для размышления

• Коррелированный подзапрос в WHERE выполняется N раз. При каких условиях это может быть быстрее JOIN?
• NOT IN безопасен только с NOT NULL столбцами. Как проверить через EXPLAIN ANALYZE, что планировщик правильно обработал NULL?
• В каких случаях derived table в FROM предпочтительнее CTE с точки зрения планировщика PostgreSQL?

Связанные уроки

• pg-07-joins — Many subqueries can be rewritten as JOINs and vice versa; knowing both lets you pick for readability and performance
• pg-10-cte — CTEs are named subqueries; mastering inline subqueries is prerequisite to understanding CTE semantics
• pg-08-aggregation — Scalar subqueries in HAVING are a common pattern; aggregation context needed
• pg-06-select — Subqueries are nested SELECTs; basic SELECT syntax must be solid first
• db-05-sql-basics