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


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


Подзапрос, который зависит от внешней строки

До сих пор подзапрос выполнялся один раз и не зависел от внешнего запроса. Но подзапрос может использовать столбцы внешнего запроса.

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

Сколько учеников в каждом классе?

SELECT c.name,
       (SELECT COUNT(*) FROM students s WHERE s.class_id = c.id) AS students_count
FROM classes c
ORDER BY c.name;

Результат:

namestudents_count
7А3
7Б4
8А4
9А0

Для каждой строки таблицы classes (псевдоним c) подзапрос считает учеников, у которых class_id равен c.id этой строки.

Обратите внимание на 9А: в нём 0 учеников, и он есть в результате. Внутренний JOIN с GROUP BY потерял бы его: у 9А нет ни одной пары с учениками.

Через LEFT JOIN и COUNT(s.id) 9А сохранится — это другой способ решить ту же задачу.

Всегда указывайте, из какой таблицы столбец

В коррелированных подзапросах давайте таблицам псевдонимы (c, s) и пишите их перед каждым столбцом. Вот что будет без них:

SELECT name,
       (SELECT COUNT(*) FROM students WHERE class_id = id) AS students_count
FROM classes
ORDER BY name;

Результат:

namestudents_count
7А1
7Б1
8А1
9А1

У каждого класса получилась единица, и SQLite не выдал ошибки. Столбец без псевдонима SQLite ищет сначала в таблицах самого подзапроса, и только потом во внешнем запросе.

В students есть свой столбец id. Поэтому class_id = id сравнило класс ученика с номером того же ученика, и подзапрос перестал зависеть от класса.


EXISTS — есть ли хотя бы одна строка

EXISTS (подзапрос) истинно, если подзапрос вернул хотя бы одну строку, и ложно, если не вернул ни одной.

Что именно выбирает подзапрос, неважно, поэтому обычно пишут SELECT 1. EXISTS почти всегда используют с коррелированным подзапросом.

Ученики, у которых есть хотя бы одна пятёрка по информатике (у информатики id = 3):

SELECT s.name
FROM students s
WHERE EXISTS (SELECT 1
              FROM grades g
              WHERE g.student_id = s.id
                AND g.subject_id = 3
                AND g.grade = 5)
ORDER BY s.name;

Результат:

name
Анна Смирнова
Глеб Соколов
Жанна Морозова
Кирилл Попов

NOT EXISTS — нет ни одной строки

Классы без учеников. С NOT IN здесь была ловушка из-за NULL у Лизы Орловой.

С NOT EXISTS ловушки нет: подзапрос ищет ученика именно этого класса, а NULL ни с каким классом не совпадёт.

SELECT c.name
FROM classes c
WHERE NOT EXISTS (SELECT 1 FROM students s WHERE s.class_id = c.id);

Результат:

name
9А

Условие «все»: двойное отрицание

Как найти учеников, у которых все оценки — четвёрки и пятёрки? В SQLite нет оператора «для всех».

Но «все оценки не ниже 4» — то же самое, что «нет ни одной оценки ниже 4». Это NOT EXISTS:

SELECT s.name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM grades g
                  WHERE g.student_id = s.id AND g.grade < 4)
ORDER BY s.name;

Результат:

name
Анна Смирнова
Дина Козлова
Жанна Морозова
Ирина Лебедева
Лиза Орлова
Максим Егоров

«Все такие» — это «нет ни одного не такого».

В результате оказалась Лиза Орлова, у которой оценок нет вообще. Формально верно: у неё нет ни одной оценки ниже 4. Но назвать её хорошисткой нельзя.

Если нужны ученики, у которых оценки есть, добавьте второе условие — EXISTS:

SELECT s.name
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM grades g
                  WHERE g.student_id = s.id AND g.grade < 4)
  AND EXISTS (SELECT 1 FROM grades g
              WHERE g.student_id = s.id)
ORDER BY s.name;

Результат:

name
Анна Смирнова
Дина Козлова
Жанна Морозова
Ирина Лебедева
Максим Егоров

IN или EXISTS?

  • IN удобен, когда подзапрос сам даёт готовый список значений: «номера учеников с двойками».
  • EXISTS удобен, когда нужно проверить связь с текущей строкой, особенно по нескольким условиям сразу.
  • Для отрицания надёжнее NOT EXISTS: он не ломается из-за NULL, в отличие от NOT IN.

Коротко

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

Подзапрос пока стоял в WHERE или в списке столбцов. Но его результат — таблица, а из таблицы можно выбирать.