Подзапросы: скалярные и коррелированные

Подзапросы: скалярные, в IN и коррелированные — что это и чем они различаются.

Подзапрос — это запрос внутри другого запроса. Скалярный возвращает одно значение, в IN — список, коррелированный — зависит от внешней строки.

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

Возвращает ровно одно значение и подставляется туда, где ждут число — в SELECT или в условие. Например, сравним зарплату каждого со средней по компании:

CREATE TABLE emp (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO emp (name, salary) VALUES
    ('Аня',120000), ('Борис',80000), ('Вера',100000), ('Гена',60000);

SELECT name, salary,
       (SELECT CAST(AVG(salary) AS INTEGER) FROM emp) AS srednyaya
FROM emp
WHERE salary > (SELECT AVG(salary) FROM emp)   -- средняя = 90000
ORDER BY salary DESC;

Вывод:

Аня|120000|90000
Вера|100000|90000

Средняя по компании — 90000. В выборку попали те, кто получает больше: Аня и Вера. Подзапрос (SELECT AVG(salary) FROM emp) вернул одно число и сработал и в SELECT, и в WHERE.

Подзапрос в IN

Возвращает целый столбец значений, по которому проверяют вхождение. «Клиенты, сделавшие хоть один крупный заказ»:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, amount INTEGER);
INSERT INTO orders (customer_id, amount) VALUES (1,900), (2,100), (1,200);

SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE amount > 500)
ORDER BY name;

Вывод:

Аня

Подзапрос вернул список customer_id с крупными заказами — только id=1. Поэтому в результате одна Аня.

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

Самый каверзный вид. Он зависит от текущей строки внешнего запроса и пересчитывается для каждой. Узнаётся по ссылке на внешнюю таблицу внутри подзапроса. Посчитаем число заказов каждого клиента:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO customers (id, name) VALUES (1,'Аня'), (2,'Борис'), (3,'Вера');

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
INSERT INTO orders (customer_id) VALUES (1), (1), (2);

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

Вывод:

Аня|2
Борис|1
Вера|0

Внутри подзапроса есть c.id — ссылка на строку внешнего запроса. Для каждого клиента подзапрос считает свой COUNT(*): Аня — 2, Борис — 1, Вера — 0. Из-за пересчёта на каждую строку коррелированные подзапросы могут быть медленными на больших данных.

Итог

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