Подзапросы и CASE: запрос в запросе и условия в столбцах

Подзапрос: ответ одного запроса внутри другого

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

SELECT title, price FROM books
WHERE price > (SELECT AVG(price) FROM books);

title                   | price
Краткая история времени | 720
Sapiens                 | 890
Чистый код              | 1200

Подзапрос SELECT AVG(price) FROM books вернул одно число — 690, — и условие превратилось в price > 690. Такой подзапрос с одним значением можно ставить везде, где ожидается число: в WHERE, в SELECT, в HAVING. Так же находят самую дорогую книгу целиком, а не только её цену: WHERE price = (SELECT MAX(price) FROM books).

Подзапрос со списком: IN

Если подзапрос возвращает столбец значений, его используют вместе с IN. Кто из клиентов покупал фэнтези?

SELECT name FROM customers
WHERE id IN (
  SELECT customer_id FROM orders
  WHERE book_id IN (SELECT id FROM books WHERE genre = 'фэнтези')
);

name
Борис
Вера
Дина

Читать удобно изнутри наружу: самый вложенный запрос находит id книг-фэнтези, средний — клиентов с заказами этих книг, внешний — их имена. Тот же ответ можно получить через JOIN, но подзапрос часто ближе к тому, как вопрос звучит словами.

NOT IN находит обратное — например, клиентов без заказов: WHERE id NOT IN (SELECT customer_id FROM orders) вернёт Егора. Одна оговорка: если подзапрос вернёт хотя бы один NULL, NOT IN не найдёт ничего. Поэтому для поиска записей без пары надёжнее приём из прошлого урока — LEFT JOIN и IS NULL.

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

Какой средний клиент тратит в магазине? Сначала нужно посчитать сумму каждого клиента, потом взять среднее от этих сумм — два уровня агрегатов. Первый уровень делает подзапрос, а внешний запрос работает с его результатом как с обычной таблицей:

SELECT ROUND(AVG(total)) AS avg_per_customer
FROM (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  WHERE status <> 'отменён'
  GROUP BY customer_id
) AS t;

avg_per_customer
1056.0

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

Подзапросу после FROM дают имя — здесь t. В MySQL без него запрос не выполнится, в PostgreSQL имя стало необязательным только с 16-й версии, а SQLite обходится без него. Пишите имя всегда — так запрос будет работать в любой базе.

CASE: условие внутри столбца

Иногда нужно не отобрать строки, а подписать их: дешёвая книга или дорогая, новый клиент или постоянный. Для этого есть CASE:

SELECT title, price,
       CASE WHEN price >= 800 THEN 'дорогая'
            WHEN price >= 500 THEN 'средняя'
            ELSE 'недорогая'
       END AS price_level
FROM books
ORDER BY price DESC;

title                             | price | price_level
Чистый код                        | 1200  | дорогая
Sapiens                           | 890   | дорогая
Краткая история времени           | 720   | средняя
Гарри Поттер и философский камень | 650   | средняя
Мастер и Маргарита                | 590   | средняя
Хоббит                            | 540   | средняя
Идиот                             | 480   | недорогая
Преступление и наказание          | 450   | недорогая

Условия проверяются сверху вниз, и срабатывает первое подходящее. Поэтому строгие условия ставят выше: если поменять ветки местами, книга за 1200 рублей станет «средней» — она ведь тоже больше 500. Если ELSE не написать, для неподошедших строк CASE вернёт NULL.

CASE внутри SUM превращается в счётчик по условию: каждая строка даёт 1 или 0, а сумма единиц — количество подходящих строк.

SELECT COUNT(*) AS all_orders,
       SUM(CASE WHEN status = 'доставлен' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'отменён' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

all_orders | delivered | cancelled
9          | 6         | 1

Так одной строкой собирают сводку по нескольким условиям сразу.

SELECT c.name,
       SUM(o.amount) AS total,
       CASE WHEN SUM(o.amount) >= 1500 THEN 'постоянный'
            WHEN SUM(o.amount) >= 600 THEN 'обычный'
            ELSE 'новичок'
       END AS segment
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
WHERE o.status <> 'отменён'
GROUP BY c.id, c.name
ORDER BY total DESC;

name  | total | segment
Борис | 1740  | постоянный
Анна  | 1310  | обычный
Вера  | 1130  | обычный
Дина  | 650   | обычный
Глеб  | 450   | новичок