Подзапросы глубоко: скалярные, коррелированные, EXISTS

Скалярные, коррелированные подзапросы и EXISTS — три разных инструмента с разной ценой.

Скалярный подзапрос возвращает одно значение и вычисляется один раз. Коррелированный ссылается на внешнюю строку и пересчитывается для каждой из них. EXISTS проверяет лишь наличие хотя бы одной строки и останавливается на первой.

Скалярный подзапрос: одно значение

Подзапрос в скобках, возвращающий ровно одно число, можно подставлять прямо в SELECT или WHERE. Здесь сравниваем цену каждого товара с общей средней.

CREATE TABLE product(name TEXT, category TEXT, price INTEGER);
INSERT INTO product VALUES
 ('Мышь','Периферия',20),('Клавиатура','Периферия',40),('Коврик','Периферия',10),
 ('SSD','Накопители',60),('HDD','Накопители',30);

SELECT name, price,
       (SELECT ROUND(AVG(price),1) FROM product) AS avg_all
FROM product
WHERE price > (SELECT AVG(price) FROM product);

Вывод:

name        price  avg_all
----------  -----  -------
Клавиатура  40     32.0
SSD         60     32.0

Средняя (32) считается один раз для всей таблицы — это дёшево. Остались товары дороже среднего.

Коррелированный подзапрос: зависит от внешней строки

Теперь сравним цену не с общей средней, а со средней в своей категории. Подзапрос ссылается на p.category внешней строки — значит, выполняется заново для каждого товара.

CREATE TABLE product(name TEXT, category TEXT, price INTEGER);
INSERT INTO product VALUES
 ('Мышь','Периферия',20),('Клавиатура','Периферия',40),('Коврик','Периферия',10),
 ('SSD','Накопители',60),('HDD','Накопители',30);

SELECT name, category, price
FROM product p
WHERE price > (SELECT AVG(price) FROM product WHERE category = p.category);

Вывод:

name        category    price
----------  ----------  -----
Клавиатура  Периферия   40
SSD         Накопители  60

Клавиатура (40) дороже средней по «Периферии» (≈23), SSD (60) — дороже средней по «Накопителям» (45). Цена производительности: такой подзапрос логически выполняется на каждую внешнюю строку, поэтому на больших таблицах он может быть тяжёлым. Часто его выгоднее переписать через оконную функцию (AVG(price) OVER (PARTITION BY category)) или JOIN с агрегатом.

EXISTS: только проверка наличия

Когда нужно узнать лишь «есть ли хоть одна связанная строка», EXISTS эффективнее, чем считать их количество: он останавливается на первом совпадении. Внутри принято писать SELECT 1 — конкретные колонки не важны.

CREATE TABLE customer(id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customer VALUES (1,'Аня'),(2,'Борис'),(3,'Вика');
CREATE TABLE orders(id INTEGER PRIMARY KEY, customer_id INTEGER, total INTEGER);
INSERT INTO orders VALUES (1,1,500),(2,1,300),(3,3,100);

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

Вывод:

name
----
Аня
Вика

Борис в результат не попал — у него нет заказов. Зеркальный NOT EXISTS вернёт как раз клиентов без заказов.

EXISTS vs IN vs COUNT

Итог

• Скалярный подзапрос даёт одно значение и (если не коррелирован) считается один раз — дёшево.
• Коррелированный подзапрос пересчитывается на каждую внешнюю строку — на больших данных это узкое место.
• EXISTS проверяет наличие и обрывается на первой строке; для «есть ли связанные записи» он лучше, чем COUNT(*) > 0.