Модуль: Подзапросы и CTE


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


Задача в два шага

Нужно вывести учеников класса 7Б. В таблице students хранится не название класса, а его номер class_id.

Можно решить в два шага: сначала узнать номер класса, потом найти учеников с этим номером.

SELECT id FROM classes WHERE name = '7Б';

Номер класса 7Б:

id
2
SELECT name FROM students WHERE class_id = 2;

Ученики с этим номером:

name
Глеб Соколов
Дина Козлова
Егор Новиков
Максим Егоров

Неудобно: номер 2 пришлось вписать вручную, и если базу изменят, запрос сломается. Оба шага можно соединить: поставить первый запрос прямо на место числа 2.


Подзапрос в круглых скобках

Подзапрос записывается в круглых скобках. Внешний запрос использует его результат так, будто это обычное значение или таблица.

SELECT name
FROM students
WHERE class_id = (SELECT id FROM classes WHERE name = '7Б');

Результат:

name
Глеб Соколов
Дина Козлова
Егор Новиков
Максим Егоров

Как это работает:

  1. Сначала выполняется подзапрос (SELECT id FROM classes WHERE name = '7Б'). Он возвращает одно число — 2.
  2. Это число подставляется во внешний запрос, и получается WHERE class_id = 2.

Скалярный подзапрос — подзапрос, который возвращает ровно одно значение: одну строку и один столбец.

Его можно писать везде, где можно написать число или строку: в WHERE рядом с =, <, >, в списке столбцов после SELECT, внутри выражений.

Скалярный подзапрос — одно значение: ставь его туда, где нужно одно число.


Сравнение со средним

Сколько в школе оценок выше среднего балла? Так написать нельзя:

SELECT COUNT(*) FROM grades WHERE grade > AVG(grade);

SQLite ответит ошибкой misuse of aggregate: AVG(). Агрегатные функции нельзя использовать в WHERE.

Причина в том, что WHERE проверяет каждую строку по отдельности, а среднее считается по всем строкам сразу. Подзапрос решает задачу: он один раз считает среднее, а внешний запрос сравнивает с ним каждую оценку.

SELECT COUNT(*) AS above_avg
FROM grades
WHERE grade > (SELECT AVG(grade) FROM grades);

Результат:

above_avg
35

Скалярный подзапрос можно поставить и в список столбцов. Выведем оценки Анны Смирновой рядом со средним баллом всей школы:

SELECT grade,
       (SELECT ROUND(AVG(grade), 2) FROM grades) AS school_avg
FROM grades
WHERE student_id = 1;

Результат:

gradeschool_avg
53.96
43.96
53.96
53.96
53.96
53.96

Если подзапрос вернул не одно значение

  • Ни одной строки. Скалярный подзапрос, который ничего не нашёл, возвращает NULL. Сравнение с NULL не бывает истинным, поэтому внешний запрос вернёт пустой результат без ошибки.
  • Несколько строк. В SQLite ошибки не будет: берётся значение из первой строки, остальные отбрасываются. Какая строка первая, без ORDER BY не гарантировано.
  • Несколько столбцов. Это ошибка. SQLite сообщит row value misused (например, в сравнении в WHERE) или sub-select returns 2 columns - expected 1.

В PostgreSQL, MySQL и других СУБД подзапрос с несколькими строками на месте одного значения — ошибка. Следите, чтобы он всегда возвращал одно значение.

Для этого подходят агрегатная функция (MAX, AVG, COUNT), условие по уникальному столбцу или LIMIT 1 вместе с ORDER BY.

Коротко

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

А если подзапрос должен вернуть не одно значение, а целый список? Об этом следующий урок.