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

Подзапросы в FROM и коррелированные подзапросы: inline-представления, EXISTS и ссылка на внешний запрос изнутри подзапроса.

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

Подзапрос в FROM (inline-представление)

Запрос в скобках в разделе FROM называют «встроенным представлением» или derived table. Он обязан иметь псевдоним:

-- Сначала считаем среднюю зарплату по отделам,
-- затем фильтруем только «дорогие» отделы (avg >= 6000)
SELECT dept_id, avg_salary
FROM (
  SELECT dept_id, ROUND(AVG(salary), 0) AS avg_salary
  FROM employees
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS dept_stats
WHERE avg_salary >= 6000
ORDER BY avg_salary DESC;

Результат:

dept_id | avg_salary
--------+-----------
5       | 8000
3       | 7200
1       | 6500

В SQLite псевдоним подзапроса можно задать без ключевого слова AS, но с ним — яснее.

Объединение подзапроса в FROM с JOIN

-- Добавляем название отдела к статистике
SELECT d.dept_name, s.avg_salary
FROM (
  SELECT dept_id, ROUND(AVG(salary), 0) AS avg_salary
  FROM employees
  WHERE dept_id IS NOT NULL
  GROUP BY dept_id
) AS s
JOIN departments AS d ON d.dept_id = s.dept_id
ORDER BY s.avg_salary DESC;

Результат:

dept_name       | avg_salary
----------------+-----------
Sales           | 8000
Finance         | 7200
Administration  | 6500
Human Resources | 5000

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

Коррелированный подзапрос ссылается на столбцы внешнего запроса. Он исполняется отдельно для каждой строки внешнего результата:

-- Для каждого сотрудника выводим, на сколько его зарплата
-- отличается от средней по его отделу
SELECT
  e.emp_name,
  e.salary,
  e.dept_id,
  e.salary - (
    SELECT ROUND(AVG(e2.salary), 0)
    FROM employees AS e2
    WHERE e2.dept_id = e.dept_id   -- ссылка на внешний запрос
  ) AS diff_from_avg
FROM employees AS e
WHERE e.dept_id IS NOT NULL
ORDER BY diff_from_avg DESC;

Результат:

emp_name     | salary | dept_id | diff_from_avg
-------------+--------+---------+--------------
Sarah Connor | 8000   | 5       | 0
Rick Deckard | 7200   | 3       | 0
Tony Montana | 6500   | 1       | 0
Ethan Hunt   | 5000   | 4       | 0

В нашей демо-БД в каждом отделе по одному сотруднику, поэтому разница нулевая — но механизм работает именно так. В реальной БД с несколькими людьми в отделе вы увидите ненулевые отклонения.

EXISTS

EXISTS — специальная форма коррелированного подзапроса: возвращает TRUE, если подзапрос вернул хотя бы одну строку:

-- Отделы, в которых есть хотя бы один сотрудник
SELECT dept_name
FROM departments AS d
WHERE EXISTS (
  SELECT 1
  FROM employees AS e
  WHERE e.dept_id = d.dept_id
)
ORDER BY dept_name;

Результат:

dept_name
---------------
Administration
Finance
Human Resources
Sales

Отдел «Customer Service» (dept_id = 2) не попал в список — у него нет сотрудников в таблице.

Коротко

• Подзапрос в FROM — временная таблица; требует псевдонима.
• Такой подзапрос можно объединять с другими таблицами через JOIN.
• Коррелированный подзапрос ссылается на внешний запрос и выполняется для каждой его строки.
• EXISTS проверяет, вернул ли подзапрос хотя бы одну строку.