Урок 9. Подзапросы

Подзапрос — запрос SELECT в скобках внутри другого запроса. Он нужен, когда для ответа сначала надо что-то посчитать: среднюю цену, список покупателей, наибольшую сумму. Урок работает с таблицами products, customers и orders из прошлых уроков.

Подзапрос-значение

Товары дороже средней цены. Средняя цена заранее неизвестна — её посчитает подзапрос:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

Сначала выполняется подзапрос в скобках и даёт одно число — 38.375. Затем внешний запрос сравнивает с ним каждую цену. Так же находится самый дорогой товар целиком, со всеми столбцами:

SELECT name, price FROM products
WHERE price = (SELECT MAX(price) FROM products);

Если подзапрос в таком месте вернёт несколько строк, PostgreSQL ответит ошибкой more than one row returned by a subquery used as an expression, MySQL — Subquery returns more than 1 row. SQLite ошибки не выдаст и молча возьмёт первую строку — результат будет неверным без всякого предупреждения. Подзапрос после =, > или < должен возвращать ровно одно значение.

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

Когда подзапрос возвращает столбец значений, с ним работает IN. Покупатели, которые заказывали ручки (товар номер 1):

SELECT name, city
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE product_id = 1);

Подзапрос вернул номера 1 и 3, внешний запрос выбрал покупателей с этими номерами. Если бы номер в списке повторялся, покупатель всё равно попал бы в ответ один раз. Товары, которые не заказывал никто:

SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM orders);

Ловушка NOT IN и NULL

Если в списке подзапроса окажется хотя бы один NULL, NOT IN не вернёт ни одной строки. Сравнение 5 NOT IN (1, NULL) даёт не «да», а «неизвестно» (NULL): вдруг неизвестное значение и есть 5? В учебной таблице product_id объявлен NOT NULL, и всё работает. Но если столбец подзапроса допускает NULL, пишите NOT EXISTS или добавьте в подзапрос WHERE столбец IS NOT NULL.

EXISTS — есть ли хоть одна строка

EXISTS (подзапрос) верно, если подзапрос нашёл хотя бы одну строку. Что он вернул — не важно, поэтому там часто пишут SELECT 1. Покупатели без заказов:

SELECT name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Подзапрос ссылается на c.id — столбец внешнего запроса. Такой подзапрос называется коррелированным (связанным): он выполняется заново для каждой строки внешнего запроса, со своим c.id. NOT EXISTS правильно работает с NULL и обычно не медленнее NOT IN.

Покупатели, у которых был хоть один крупный заказ — от 10 штук:

SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.qty >= 10
);

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

Подзапрос может стоять и в SELECT — тогда он считается для каждой строки ответа. Число заказов у каждого покупателя:

SELECT c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c
ORDER BY c.id;

Тот же ответ даёт LEFT JOIN с GROUP BY из урока о JOIN. Подзапрос читается проще, а на больших таблицах вариант с соединением бывает быстрее.

Самая дорогая строка в каждой группе

В уроке о группировке осталась задача: показать самый дорогой товар каждой категории с его названием. Коррелированный подзапрос решает её на любой СУБД:

SELECT name, price, category
FROM products p
WHERE price = (
    SELECT MAX(price) FROM products WHERE category = p.category
)
ORDER BY category;

Для каждого товара подзапрос находит наибольшую цену в его категории, и товар остаётся, если его цена с ней совпадает. Если в категории два товара с одинаковой наибольшей ценой, в ответ попадут оба.

Подзапрос в FROM

Результат подзапроса можно использовать как временную таблицу. Сколько в среднем заказов у одного покупателя (из тех, кто заказывал):

SELECT AVG(cnt) AS avg_orders
FROM (
    SELECT customer_id, COUNT(*) AS cnt
    FROM orders
    GROUP BY customer_id
) AS per_customer;

Внутренний запрос дал таблицу «покупатель — число заказов», внешний усреднил её столбец. Такому подзапросу дают имя после AS — здесь per_customer. Некоторые СУБД (MySQL) без имени выдают ошибку, поэтому пишите его всегда.

WITH — подзапрос с именем

Длинные подзапросы удобнее вынести вперёд и назвать. Это WITH (общее табличное выражение, CTE) — есть в SQLite, PostgreSQL и MySQL 8.0 и новее:

WITH spent AS (
    SELECT o.customer_id, SUM(p.price * o.qty) AS total
    FROM orders o
    JOIN products p ON p.id = o.product_id
    GROUP BY o.customer_id
)
SELECT c.name, s.total
FROM customers c
JOIN spent s ON s.customer_id = c.id
WHERE s.total > 60
ORDER BY s.total DESC;

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

Подзапросы в UPDATE и DELETE

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

UPDATE products
SET qty = qty + 100
WHERE id IN (SELECT product_id FROM orders WHERE qty >= 10);

Учебные таблицы этот запрос изменит — выполняйте его только внутри BEGIN … ROLLBACK, как в уроке об UPDATE. В MySQL подзапрос не может читать ту же таблицу, которую меняет команда: ошибка You can't specify target table … for update in FROM clause.

Коротко

• Подзапрос — SELECT в скобках внутри другого запроса.
• После =, >, < подзапрос должен вернуть одно значение; список — для IN.
• NOT IN с NULL в подзапросе не возвращает ничего — надёжнее NOT EXISTS.
• Коррелированный подзапрос ссылается на внешний запрос и выполняется для каждой его строки.
• Подзапросу в FROM дают имя; длинные подзапросы удобно вынести в WITH.

Проверьте себя