Подзапросы: скалярный и в 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 внутрь подзапроса — хорошая страховка в обоих случаях.