SQL для тестировщика – Часть 4: Подзапросы, UNION, CASE

QA❤️4Life

Четвёртая часть расширенной шпаргалки по SQL: подзапросы, UNION и CASE – запросы внутри запросов, склейка результатов и условная логика прямо в SELECT.

Канал: QA❤️4Life
Автор: Евгений Гусинец

SQL для тестировщика – Часть 4 из 5: Подзапросы, UNION, CASE

Демо-схема для всех примеров серии:

users (id, email, status, created_at)
orders (id, user_id, sum, status, created_at)
payments (id, order_id, amount, created_at)

Скалярный подзапрос в WHERE

Что делает. Возвращает одно значение, с которым сравнивают внешние строки.

SELECT id, sum
FROM orders
WHERE sum > (SELECT AVG(sum) FROM orders);

Зачем. «Выше среднего», «дороже максимального платежа» – сравнение с вычисленной величиной без хардкода.

Подзапрос с IN

Что делает. Возвращает столбец значений для проверки принадлежности.

SELECT id, email
FROM users
WHERE id IN (SELECT user_id
             FROM orders
             WHERE sum > 10000);

Зачем. «Пользователи, у которых были крупные заказы» – сегментация для выборки данных под тест.

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

Что делает. Ссылается на строку внешнего запроса и выполняется для каждой строки.

SELECT o.id, o.sum
FROM orders o
WHERE o.sum >
  (SELECT AVG(o2.sum)
   FROM orders o2
   WHERE o2.user_id = o.user_id);

Зачем. «Заказ дороже среднего по своему пользователю». На больших таблицах медленно, для разовых проверок данных – нормально.

Скалярный подзапрос в SELECT

Что делает. Считает агрегат по связанным данным прямо в списке столбцов.

SELECT u.email,
  (SELECT COUNT(*) FROM orders o
   WHERE o.user_id = u.id) AS orders_cnt
FROM users u;

Зачем. Список пользователей с числом заказов в одной строке результата.

EXISTS / NOT EXISTS

Что делает. Проверка существования хоть одной подходящей строки; возвращает TRUE/FALSE.

SELECT u.id
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.user_id = u.id);

Зачем. Похож на IN, но надёжнее на NULL и часто быстрее на больших данных. NOT EXISTS – безопасная замена NOT IN (см. ловушку ниже).

Ловушка: NOT IN + NULL = пустой результат

Что делает. Если в подзапросе встретился NULL, NOT IN возвращает ничего – молча, без ошибки. Вот так ловушка стреляет: в user_id могут лежать NULL, и запрос вернёт пустоту:

SELECT id
FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

Зачем. Знать, почему «запрос вдруг вернул пустоту». Безопасные альтернативы – фильтр внутри подзапроса:

SELECT id
FROM users
WHERE id NOT IN (SELECT user_id
                 FROM orders
                 WHERE user_id IS NOT NULL);

или NOT EXISTS из раздела выше.

Подзапрос в FROM

Что делает. Считает агрегат первым, фильтр – поверх результата. Обязателен алиас.

SELECT status, cnt
FROM (SELECT status, COUNT(*) AS cnt
      FROM orders
      GROUP BY status) s
WHERE cnt > 100;

Зачем. Фильтр по агрегату без HAVING, когда логика становится сложной.

UNION / UNION ALL

Что делают. Склеивают результаты двух запросов. UNION убирает дубли и сортирует, UNION ALL оставляет всё как есть и работает быстрее.

SELECT id FROM users
UNION ALL
SELECT user_id FROM orders;

Зачем. Типовой кейс – сверка миграции: склеить id из старой и новой таблицы, найти те, что встречаются один раз, – они есть только с одной стороны:

SELECT id, COUNT(*) AS sides
FROM (SELECT id FROM users_old
      UNION ALL
      SELECT id FROM users_new) t
GROUP BY id
HAVING COUNT(*) = 1;

Сестры EXCEPT/INTERSECT (в Oracle – MINUS) оставляют разницу и пересечение наборов.

CASE: условная логика

Что делает. Возвращает значение по первому выполненному условию. Три роли:

Метка в SELECT:

SELECT id,
  CASE WHEN sum > 10000
       THEN 'крупный'
       ELSE 'обычный' END AS size
FROM orders;

Фильтр в WHERE: WHERE CASE WHEN ... THEN 1 ELSE 0 END = 1 – когда выражение нельзя записать проще.

Условная агрегация – pivот по статусам одним запросом:

SELECT
  SUM(CASE WHEN status = 'paid'
           THEN 1 ELSE 0 END) AS paid_cnt,
  SUM(CASE WHEN status = 'pending'
           THEN 1 ELSE 0 END) AS pending_cnt
FROM orders;

Резюме части 4

Скалярный подзапрос – одно значение, IN-подзапрос – столбец.
EXISTS/NOT EXISTS надёжнее IN/NOT IN на NULL.
NOT IN с NULL в списке = пустой результат без ошибки.
UNION убирает дубли, UNION ALL – честная и быстрая склейка.
CASE – метка, фильтр и условная агрегация в одном операторе.