Подзапросы с несколькими строками и одним столбцом

Одноколоночный подзапрос с несколькими строками

Такой подзапрос возвращает набор значений. С ним используют IN, EXISTS, а также ANY или ALL.

SELECT id, email
FROM users
WHERE id IN (
    SELECT user_id
    FROM orders
    WHERE state = 'PAID'
);

IN здесь выражает полусоединение: нужны пользователи, для которых есть подходящий заказ.

ANY и ALL

-- Заказ дороже хотя бы одного заказа пользователя 7.
WHERE total > ANY (
    SELECT total FROM orders WHERE user_id = 7
)
 
-- Заказ дороже каждого заказа пользователя 7.
WHERE total > ALL (
    SELECT total FROM orders WHERE user_id = 7
)

Для пустого набора > ANY (...) ложно, а > ALL (...) истинно. Наличие NULL добавляет UNKNOWN, поэтому границы должны быть осмыслены.

NOT IN и NULL

-- Опасно, если blocked_users.user_id допускает NULL.
WHERE id NOT IN (SELECT user_id FROM blocked_users)

Из-за трехзначной логики одна строка с NULL может сделать предикат UNKNOWN для всех кандидатов. Обычно безопаснее:

WHERE NOT EXISTS (
    SELECT 1
    FROM blocked_users AS b
    WHERE b.user_id = users.id
)

Ловушки

• IN не гарантирует порядок.
• Дубликаты внутри IN не меняют логический результат, но могут влиять на работу плана.
• Не нужно добавлять DISTINCT автоматически: оптимизатор часто сам строит semi join.

Связано: Оператор EXISTS, Операторы IS NULL BETWEEN IN, Что лучше подзапросы или JOIN.

Вопросы

Чем IN отличается от =? IN сравнивает значение с набором, = — с одним значением.

Почему опасен NOT IN? NULL в наборе приводит сравнение к UNKNOWN.

Что выбрать для проверки отсутствия связанных строк? Обычно NOT EXISTS.