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

Подзапрос это запрос внутри запроса. На собеседовании его спрашивают в двух формах: «напиши запрос, где без подзапроса не обойтись» и «чем коррелированный подзапрос отличается от обычного». Второй вопрос проверяет, понимаешь ли ты, сколько раз база выполняет внутренний запрос. Все примеры работают на учебной базе блока.

Как спрашивают

Найди сотрудников, которые зарабатывают больше среднего по компании. А теперь больше среднего по своему отделу. Чем эти два запроса отличаются?

Короткий ответ

Подзапрос подставляет результат одного запроса внутрь другого. Скалярный подзапрос возвращает одно значение, и его можно поставить везде, где ждут число или строку. Табличный возвращает набор строк и стоит во FROM, IN или EXISTS. Коррелированный подзапрос ссылается на колонку внешнего запроса, поэтому логически выполняется заново для каждой внешней строки: «среднее по отделу этого сотрудника» у каждого своё.

Разбор: справка из бухгалтерии

Представь, что ты разбираешь стопку анкет сотрудников. Для первого вопроса ты один раз звонишь в бухгалтерию: «какая средняя зарплата по компании?», записываешь число на листке и дальше сравниваешь с ним каждую анкету. Это обычный подзапрос: выполнился один раз, дал одно число.

Для второго вопроса так не выйдет. На каждой анкете написан свой отдел, и для каждой ты звонишь заново: «какая средняя по отделу 1?», «а по отделу 2?». Вопрос зависит от анкеты в руках. Это и есть коррелированный подзапрос.

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

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

SELECT name, salary
FROM employees
WHERE salary > (SELECT avg(salary) FROM employees)
ORDER BY salary DESC;

name  | salary
------+-------
Анна  | 300000
Борис | 250000
Вера  | 250000

Средняя по компании 207 143, выше неё трое. Внутренний запрос не зависит от внешней строки, база посчитает его один раз.

Скалярный подзапрос можно поставить и в SELECT, тогда он даёт колонку:

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

name  | orders_count
------+-------------
Ольга |            3
Павел |            1
Роман |            1
Света |            0

Заметь o.customer_id = c.id: подзапрос ссылается на внешнюю таблицу c. Значит, он уже коррелированный, об этом ниже.

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

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

SELECT e.name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
  SELECT avg(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
)
ORDER BY e.id;

name | department_id | salary
-----+---------------+-------
Анна |             1 | 300000
Глеб |             2 | 200000

Для Анны подзапрос считает среднее по отделу 1 (266 667), для Глеба по отделу 2 (175 000). Таблица employees встречается дважды, поэтому у копий разные псевдонимы e и e2, как при переименовании в алгебре.

Табличный подзапрос

Подзапрос во FROM возвращает таблицу, из которой выбирают дальше. Ему обязательно нужен псевдоним:

SELECT avg(total)
FROM (
  SELECT customer_id, sum(amount) AS total
  FROM orders
  GROUP BY customer_id
) t;

Ответ 6400: средняя сумма заказов на клиента, у которого есть заказы. Два агрегата подряд, «сначала сумма по клиенту, потом среднее сумм», одним GROUP BY не записать, а подзапросом можно. Когда таких слоёв становится три и больше, их читают через CTE, это следующий урок.

EXISTS и IN

EXISTS проверяет, вернул ли подзапрос хоть одну строку:

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

Ответ: Ольга, Павел, Роман. Света без заказов выпала. Обратный вопрос, «клиенты без заказов», и ловушка NOT IN с NULL разобраны в уроке про анти-джойны.