Подзапросы

«Какие товары дороже средней цены?» В этом вопросе два шага: сначала найти среднюю цену (293,56), затем сравнить с ней. Число можно вписать в запрос вручную, но завтра цены изменятся, и запрос устареет. Лучше сделать оба шага в одном запросе. Для этого используют подзапрос.

Подзапрос в WHERE

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

name | price
Laptop | 1450
Smartphone | 899.99
Monitor | 310

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

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

name | price
Laptop | 1450

Подзапросы с IN и NOT IN

Если подзапрос возвращает много строк в одном столбце, его можно использовать как список для IN. Найдём учеников, записанных на курсы физики. Подзапросы можно вкладывать друг в друга: самый внутренний возвращает номера курсов физики, средний — номера записанных на них учеников.

SELECT first_name, last_name
FROM students
WHERE id IN (
  SELECT student_id
  FROM enrollments
  WHERE course_id IN (SELECT id FROM courses WHERE subject = 'Physics')
)
ORDER BY id;

first_name | last_name
Murad | Əliyev
Elvin | Quliyev
Rəşad | Kərimov
Fidan | Cəfərova

SELECT name, category
FROM products
WHERE id NOT IN (SELECT product_id FROM orders)
ORDER BY id;

name | category
Monitor | Electronics

SELECT COUNT(*) AS found
FROM products
WHERE id NOT IN (1, 2, NULL);

found
0

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

Коррелированный подзапрос ссылается на столбец внешнего запроса, например o.customer_id = c.id. Логически он вычисляется заново для каждой строки внешнего запроса. EXISTS (...) истинно, когда подзапрос возвращает хотя бы одну строку, — неважно, что в этих строках, поэтому внутри обычно пишут SELECT 1.

SELECT c.name, c.country
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  JOIN products AS p ON p.id = o.product_id
  WHERE o.customer_id = c.id
    AND p.category = 'Electronics'
)
ORDER BY c.id;

name | country
Anar Mustafayev | Azerbaijan
Lalə Əhmədova | Azerbaijan
Emre Yılmaz | Türkiye
Zəhra Hüseynli | Azerbaijan

Коррелированный подзапрос может стоять и в SELECT — тогда он вычисляет одно значение для каждой строки. Ниже видно, на сколько курсов записан каждый десятиклассник и каков его лучший балл.

SELECT s.first_name,
       (SELECT COUNT(*)   FROM enrollments AS e WHERE e.student_id = s.id) AS courses,
       (SELECT MAX(score) FROM enrollments AS e WHERE e.student_id = s.id) AS best
FROM students AS s
WHERE s.grade = 10
ORDER BY s.id;

first_name | courses | best
Murad | 2 | 81
Rəşad | 2 | 99
Səbinə | 2 | 88

SELECT e.student_id, e.course_id, e.score
FROM enrollments AS e
WHERE e.score > (
  SELECT AVG(e2.score)
  FROM enrollments AS e2
  WHERE e2.course_id = e.course_id
)
ORDER BY e.course_id, e.score DESC;

student_id | course_id | score
11 | 1 | 97
1 | 1 | 92
3 | 2 | 95
11 | 3 | 94
2 | 3 | 81
4 | 4 | 72
5 | 5 | 93
6 | 6 | 99
3 | 7 | 90

Подзапрос в FROM и WITH

Результат подзапроса — таблица, поэтому его можно написать и в FROM, где он работает как временная таблица. Большинство СУБД требуют дать такому подзапросу псевдоним (AS t). Ниже мы сначала находим среднее по каждому курсу, а затем среднее этих средних и лучшее из них.

SELECT ROUND(AVG(avg_score), 1) AS avg_of_courses,
       ROUND(MAX(avg_score), 1) AS best_course
FROM (
  SELECT course_id, AVG(score) AS avg_score
  FROM enrollments
  GROUP BY course_id
) AS t;

avg_of_courses | best_course
82 | 92.7

Когда подзапросов, вложенных друг в друга, становится много, запрос трудно читать. Конструкция WITH (CTE — обобщённое табличное выражение) позволяет заранее дать подзапросу имя и затем использовать его как обычную таблицу. WITH поддерживают SQLite, PostgreSQL, SQL Server и MySQL начиная с версии 8.0.

WITH course_avg AS (
  SELECT course_id, ROUND(AVG(score), 1) AS avg_score
  FROM enrollments
  GROUP BY course_id
)
SELECT c.title, ca.avg_score
FROM course_avg AS ca
JOIN courses AS c ON c.id = ca.course_id
WHERE ca.avg_score > 85
ORDER BY ca.avg_score DESC;

title | avg_score
Python Basics | 92.7
World History | 90.5
Algebra | 87.3

SELECT first_name, age
FROM students
WHERE age > ( /* average age here */ )
ORDER BY age DESC, first_name;

first_name | age
Elvin | 17
Fidan | 17
Tural | 17
Murad | 16
Rəşad | 16
Səbinə | 16

SELECT name
FROM products
WHERE id IN (
  -- product ids from the orders of Turkish customers
)
ORDER BY name;

name
Chess set
Pen set
Smartphone