Подзапросы: скалярный и в WHERE

На предыдущих уроках вы соединяли таблицы через JOIN, чтобы добраться до данных из нескольких источников. Но бывает задача другого рода: нужно не вывести столбцы из второй таблицы, а использовать вычисленное из неё значение как условие фильтра. Именно для этого существуют подзапросы — запросы внутри запросов.

Скалярный подзапрос: одно значение как аргумент WHERE

Подзапрос — это обычный SELECT, обёрнутый в скобки и помещённый внутрь другого запроса. Скалярный подзапрос — частный случай: он возвращает ровно одно значение, и это значение используется там, где обычно стоит число или строка.

Самый наглядный пример — найти товар с максимальной ценой:

SELECT title
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Внутренний SELECT MAX(price) FROM products — это и есть скалярный подзапрос. MAX без GROUP BY всегда возвращает один столбец и одну строку: если таблица непустая — максимум, если пустая — NULL. Именно агрегат без GROUP BY служит надёжным способом получить гарантированно одно значение.

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

Что происходит, когда подзапрос возвращает NULL. Если таблица products пустая, MAX(price) вернёт NULL. Сравнение price = NULL в SQL всегда даёт UNKNOWN, а не TRUE, поэтому ни одна строка не пройдёт фильтр — внешний запрос вернёт пустой результат. Это логично, но нужно держать в голове.

Что происходит при нуле строк. Возьмём подзапрос, который действительно не возвращает строк:

SELECT title
FROM products
WHERE price = (SELECT price FROM products WHERE 1 = 0);

В скалярном контексте отсутствие строк превращается в NULL, поэтому сравнение не пропустит ни одного товара. Это отличается от MAX на пустом входе: агрегат без GROUP BY возвращает одну строку, но значение в ней тоже NULL.

Что происходит при нескольких строках. Скалярный подзапрос предполагает одну строку. Если написать что-то вроде WHERE price = (SELECT price FROM products) без агрегации, подзапрос вернёт много строк. В PostgreSQL это немедленная ошибка: more than one row returned by a subquery. SQLite поведёт себя мягче — возьмёт значение из первой строки и проигнорирует остальные, — но полагаться на это нельзя: запрос молча даст неверный результат на других СУБД. Агрегат снимает проблему именно потому, что схлопывает любое множество строк в одну.

Скалярный подзапрос можно поставить не только в WHERE, но и в SELECT (как вычисляемый столбец) или HAVING. Сейчас нас интересует WHERE — именно там он чаще всего решает задачу «сравнить строку с вычисленным эталоном».

Подзапрос в WHERE: товары дороже среднего

В этом примере все данные заданы внутри запроса. Его можно выполнить в SQLite или PostgreSQL без таблиц из курса:

WITH products(title, price) AS (
    VALUES ('Кабель', 500),
           ('Мышь', 1500),
           ('Клавиатура', 4000)
)
SELECT title, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

Средняя цена — 2000. В результат попадёт только Клавиатура | 4000. Внутренний SELECT вычисляет среднее, а внешний сравнивает с ним цену каждого товара. Если написать условие price > AVG(price) прямо в WHERE без подзапроса, такой запрос будет некорректным: агрегат нельзя использовать там таким способом.

Подзапрос в IN/NOT IN: фильтрация по списку и ловушка NULL

Скалярный подзапрос даёт одно значение. Но что если нужно проверить, входит ли значение в набор из нескольких строк? Тут работает IN с подзапросом.

Предположим, нужно найти клиентов, у которых есть хотя бы один заказ дороже 3000:

SELECT name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE total_amount > 3000
);

Внутренний запрос возвращает список customer_id — строк может быть сколько угодно, один столбец. Внешний запрос проверяет каждую строку customers: если её customer_id есть в этом списке, строка включается в результат. Один клиент попадёт в результат ровно один раз, даже если у него несколько подходящих заказов — это отличает IN от JOIN, который мог бы дублировать строку клиента.

NOT IN инвертирует логику: остаются клиенты, чей customer_id отсутствует в списке. Запрос «клиенты без ни одного заказа»:

SELECT name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
);

Ловушка: NULL в списке. Если подзапрос вернул хотя бы одну строку с NULL, NOT IN перестаёт работать так, как ожидается. Если проверяемое значение совпало с одним из ненулевых элементов списка, NOT IN даёт FALSE. Если совпадения нет, но в списке присутствует NULL, результат становится UNKNOWN: SQL не может доказать, что значение отличается от неизвестного элемента. Ни FALSE, ни UNKNOWN не проходят фильтр WHERE, поэтому при наличии NULL в списке NOT IN может отбросить все строки — без ошибки и предупреждения.

Защита простая: добавить WHERE customer_id IS NOT NULL внутрь подзапроса:

SELECT name
FROM customers
WHERE customer_id NOT IN (
    SELECT customer_id
    FROM orders
    WHERE customer_id IS NOT NULL
);

Для IN ловушка менее критична: NULL в списке просто не даёт совпадения, но остальные значения проверяются нормально. С NOT IN она фатальна, поэтому привычка добавлять IS NOT NULL внутрь подзапроса — хорошая страховка в обоих случаях.

Некоррелированный подзапрос: логический порядок и выбор между подзапросом и JOIN

Оба подзапроса, которые мы разбирали, — некоррелированные: внутренний SELECT не содержит ни одной ссылки на столбцы внешнего запроса. Это ключевое свойство, которое определяет логику чтения.

Логически некоррелированный подзапрос вычисляется один раз и независимо: сначала отрабатывает внутренний запрос, получает результат, а затем внешний запрос использует этот результат как готовое значение или список. Именно так нужно читать и писать такие запросы — «что вычислится внутри» → «что с этим делает снаружи».

Физически оптимизатор СУБД может выбрать любой план: переставить порядок, превратить подзапрос в соединение, использовать индекс. Это его право, и правильность результата от этого не меняется — гарантию даёт именно логический контракт некоррелированного подзапроса.

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

Когда подзапрос, а когда JOIN. Это практический выбор, и критерий один: нужны ли вам столбцы из второй таблицы в результате?

Если нужно только отфильтровать строки основной таблицы по условию на другой — подзапрос в WHERE чище и точнее. Пример: «вывести имена клиентов, у которых есть хотя бы один заказ». Здесь из orders не нужно ничего выводить — только проверить факт существования строк.

SELECT name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);

Если сделать то же самое через JOIN, каждый клиент с тремя заказами появится в результате трижды — придётся добавлять DISTINCT или GROUP BY, что усложняет запрос без необходимости.

Если же нужно вывести, например, имя клиента и сумму его заказа рядом — без JOIN не обойтись, потому что orders.total_amount не попадёт в результат через подзапрос в WHERE.

Правило коротко: подзапрос — когда вторая таблица нужна только как источник условия; JOIN — когда нужны её данные в результирующих столбцах.