Модуль 12 ⏱ ~22 мин чтения 📐 1 Mermaid диаграмм

9. Подзапросы

«Красота конвейеров Unix заключается в том, что вывод одной программы становится вводом для другой. В реляционном мире это свойство называют замкнутостью: каждый запрос порождает отношение, и к любому отношению можно обратиться с новым запросом».

Композитная природа SQL: Запрос внутри запроса

Реляционная теория Эдгара Кодда зиждется на элегантном математическом свойстве — замкнутости (closure). Это фундаментальное правило гласит: результатом применения любой реляционной операции к отношению (таблице) всегда является новое отношение (таблица).

Именно благодаря свойству замкнутости язык SQL функционирует как идеальный конструктор: мы можем взять результат выполнения одного запроса и встроить его внутрь другого туда, где синтаксис ожидает таблицу, строку или скалярное значение. Такое вложенное выражение называется подзапросом (Subquery).

Для бэкенд-инженера подзапросы — это первоклассный способ делегирования бизнес-логики и трансформации данных непосредственно ядру СУБД. Грамотное использование подзапросов позволяет кардинально сократить объем сетевого трафика (Network IO) и навсегда устранить коварную проблему 13. N+1 проблема в приложениях на Go.

Типология подзапросов

Понимание классификации подзапросов необходимо для того, чтобы предугадывать, как именно оптимизатор СУБД будет трансформировать абстрактное синтаксическое дерево (AST) в физический план исполнения.

1. Скалярные подзапросы (Scalar Subqueries)

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

SQL

-- Пример в WHERE: Найти товары, стоимость которых превышает среднюю по каталогу
SELECT name, price 
FROM products 
WHERE price > (SELECT AVG(price) FROM products);

Ловушка / Gotcha: Run-time ошибка скалярного запроса

Если ваш скалярный подзапрос в силу изменения данных неожиданно вернет более одной строки, СУБД не станет «угадывать» ваше намерение и не возьмет первую попавшуюся запись.
Транзакция немедленно оборвется с фатальной runtime-ошибкой ядра: ERROR: more than one row returned by a subquery used as an expression.
Если вы проектируете скалярный запрос и бизнес-правила допускают множественные совпадения, всегда явно защищайте выражение детерминированной сортировкой и директивой LIMIT 1.

2. Табличные подзапросы в FROM (Derived Tables)

Если подзапрос возвращает двумерный массив (множество строк и столбцов), его можно подставить в секцию FROM. В терминологии реляционной теории это называется производной таблицей (Derived Table) — временным отношением, материализуемым в памяти на время жизни запроса.

SQL

-- Пример в FROM: Предварительная агрегация перед операцией JOIN
SELECT u.email, stat.total_spent
FROM users u
JOIN (
    SELECT user_id, SUM(amount) AS total_spent 
    FROM orders 
    GROUP BY user_id
) stat ON u.id = stat.user_id;

Синтаксическое требование: Любой производной таблице в предложении FROM в обязательном порядке должен быть присвоен алиас (в примере выше — stat), иначе парсер СУБД завершит разбор синтаксической ошибкой.

В современной промышленной разработке громоздкие подзапросы во FROM принято выносить в более читаемые и композируемые общие табличные выражения — 1. CTE. WITH выражения. Под капотом оптимизатор обрабатывает их по схожим алгоритмам.

Производительность: Некоррелированные vs Коррелированные

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

Некоррелированный подзапрос (Uncorrelated)

Подзапрос называется некоррелированным, если он абсолютно автономен и не ссылается ни на один атрибут или колонку внешнего (родительского) запроса.

В выражении WHERE price > (SELECT AVG(price) FROM products) внутренний SELECT ничего не знает о внешнем контексте.

Mechanical Sympathy: Оптимизатор СУБД выполняет такой подзапрос ровно один раз в самом начале исполнения плана, сохраняет результат в оперативной памяти и затем использует вычисленную константу во всех последующих итерациях фильтрации. Алгоритмическая сложность суммируется: $O(N + M)$.

Коррелированный подзапрос (Correlated)

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

SQL

-- Извлечь пользователей и дату их самого дорогого заказа
SELECT 
    u.email,
    (
        SELECT o.created_at 
        FROM orders o 
        WHERE o.user_id = u.id -- КРИТИЧЕСКАЯ СВЯЗЬ: обращение к внешней строке u
        ORDER BY o.amount DESC 
        LIMIT 1
    ) AS max_order_date
FROM users u;

Mechanical Sympathy: На физическом уровне коррелированный подзапрос ведет себя в точности как вложенный цикл for в императивном программировании на Go. Ядро СУБД вынуждено заново запускать исполнение внутреннего подзапроса для каждой отдельной строки внешнего запроса!

Если во внешней таблице users насчитывается 100 000 записей, СУБД выполнит внутренний SELECT ... FROM orders ровно 100 000 раз! Сложность взлетает до O(N * M) (или O(N * log M) при наличии селективного индекса по внешнему ключу). Это классический алгоритм Nested Loop (см. 6. JOIN. INNER, LEFT, RIGHT).

flowchart TD
    classDef process  fill:#1e293b,stroke:#38bdf8,stroke-width:1px,color:#f8fafc
    classDef success  fill:#064e3b,stroke:#34d399,stroke-width:1px,color:#f8fafc
    classDef error    fill:#7f1d1d,stroke:#f87171,stroke-width:1px,color:#f8fafc
    classDef warning  fill:#78350f,stroke:#fbbf24,stroke-width:1px,color:#f8fafc
    classDef entry    fill:#1e3a5f,stroke:#38bdf8,stroke-width:2px,color:#f8fafc
    classDef aux      fill:#334155,stroke:#64748b,stroke-width:1px,color:#f8fafc
    classDef system   fill:#312e81,stroke:#a78bfa,stroke-width:1px,color:#f8fafc
    classDef data     fill:#132d29,stroke:#2dd4bf,stroke-width:1px,color:#f8fafc
    classDef network  fill:#881337,stroke:#fb7185,stroke-width:1px,color:#f8fafc
    classDef accent   fill:#1e1b4b,stroke:#818cf8,stroke-width:1px,color:#f8fafc
    OuterScan["Sequential Scan<br>Таблица users"]:::process -->|"Извлечение строки u.id"| InnerExecution["Выполнение подзапроса<br>с фильтром o.user_id = u.id"]
    InnerExecution -->|"Поиск по B-Tree<br>или Seq Scan"| FetchOrder["Извлечение max_order_date"]
    FetchOrder -->|"Возврат значения"| ResultRow["Формирование итоговой строки"]
    ResultRow -->|"Следующая строка"| OuterScan

Subquery Flattening: Магия оптимизатора

Может показаться, что коррелированных подзапросов нужно избегать любой ценой. В 1990-х годах это действительно было так. Однако современные оптимизаторы СУБД (PostgreSQL 12+, MySQL 8.0+) оснащены мощным математическим аппаратом преобразования реляционных выражений (см. 11. Cost based optimizer).

Когда оптимизатор встречает подзапросы с предикатами IN или EXISTS, он запускает эвристику развертывания подзапросов (Subquery Unnesting / Flattening). Оптимизатор на этапе логического планирования трансформирует синтаксическое дерево, переписывая коррелированный вложенный цикл в плоскую операцию соединения (Hash JOIN или Merge JOIN).

С точки зрения ядра современной СУБД между следующими двумя конструкциями практически нет разницы:

SQL

-- Вариант 1: Подзапрос через IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- Вариант 2: Соединение через JOIN
SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id;

В подавляющем большинстве случаев стоимостной оптимизатор построит для них абсолютно идентичный план выполнения (Semi-Join / Hash Join), избавив систему от квадратичной сложности.

Под капотом: Когда Flattening не работает

Оптимизатор не всесилен. Он вынужден капитулировать и оставить тяжелый вложенный цикл Nested Loop, если внутри коррелированного подзапроса присутствуют:

Агрегатные функции с GROUP BY;
Оконные функции (OVER (...));
Специфические комбинации LIMIT/OFFSET, которые математически невозможно безопасно слить с родительским отношением.

В подобных ситуациях оптимизация ложится на плечи инженера: подзапрос переписывают вручную, вынося агрегацию во FROM или используя конструкции LEFT JOIN с фильтрацией.

Go Idioms: Подзапросы спасают Network IO

Рассмотрим классическую архитектурную ошибку начинающего разработчика на Go.
Поставлена задача: заблокировать всех пользователей, которые не совершили ни одной покупки за последний год.

❌ Антипаттерн: Перенос логики в Go (Уничтожение сетевого стека):

// 1. Выкачиваем первичные ключи всех пользователей в память Go
rows, err := db.QueryContext(ctx, "SELECT id FROM users")
// ... сканирование в users []int64

for _, id := range users {
    // 2. Делаем отдельный сетевой вызов для КАЖДОГО пользователя (N+1 запросов!)
    var count int
    _ = db.QueryRowContext(ctx, 
        "SELECT COUNT(*) FROM orders WHERE user_id = $1 AND created_at > ...", id).Scan(&count)

    if count == 0 {
        // 3. Отправляем еще один сетевой запрос на модификацию
        _, _ = db.ExecContext(ctx, "UPDATE users SET status = 'blocked' WHERE id = $1", id)
    }
}

Этот фрагмент порождает сотни тысяч сетевых TCP-пакетов, парализует пул соединений к базе данных и заставляет Garbage Collector в Go непрерывно очищать временные структуры.

✅ Идиоматичный бэкенд: Один атомарный запрос с подзапросом:

// Отправляем РОВНО ОДИН сетевой запрос. Вся обработка выполняется ядром СУБД 
// рядом со страницами данных без накладных расходов на сеть (Zero Network IO overhead).
const query = `
    UPDATE users 
    SET status = 'blocked'
    WHERE id NOT IN (
        SELECT user_id 
        FROM orders 
        WHERE created_at > NOW() - INTERVAL '1 year'
    )
`
res, err := db.ExecContext(ctx, query)
if err != nil {
    return fmt.Errorf("bulk block users: %w", err)
}

affected, _ := res.RowsAffected()
log.Printf("Successfully blocked %d inactive users", affected)

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

Итог

Подзапросы опираются на свойство реляционной замкнутости (closure), превращая SQL в модульный декларативный язык.
Скалярные подзапросы возвращают ровно одно значение ($1 \times 1$). Защищайте их от случайных множественных совпадений с помощью LIMIT 1.
Табличные подзапросы в секции FROM (Derived Tables) требуют обязательного указания алиаса.
Различайте некоррелированные подзапросы (вычисляются 1 раз, кэшируются в памяти) и коррелированные (вычисляются для каждой строки внешнего отношения, потенциально вызывая Nested Loop со сложностью O(N*M)).
Современные CBO реализуют Subquery Flattening, автоматически преобразуя многие подзапросы в эффективные Hash JOIN.
В Go-разработке один выверенный SQL-запрос с подзапросом на порядки эффективнее, чем циклы for с обращениями к БД по сети (защита от задержек сетевого ввода-вывода).

Среди всех вариантов подзапросов чаще всего инженеры используют операторы IN и EXISTS. Какой из них работает быстрее, как оптимизатор применяет короткое замыкание (Short-circuit) и почему NOT IN таит смертельную опасность при наличии NULL? Ответы ждут нас в следующей статье: 10. EXISTS и IN.