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


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


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

Оператор IN проверяет, есть ли значение в списке: class_id IN (1, 2). Список можно получить подзапросом.

Такой подзапрос возвращает один столбец, а строк в нём может быть сколько угодно.

Какие ученики получали двойки? Номера таких учеников есть в таблице grades, а имена — в students:

SELECT name
FROM students
WHERE id IN (SELECT student_id FROM grades WHERE grade = 2)
ORDER BY name;

Результат:

name
Глеб Соколов
Егор Новиков
Захар Волков

Подзапрос вернул номера 4, 6 и 8, и внешний запрос выбрал учеников с этими номерами.

Если бы у кого-то было две двойки, его номер встретился бы в списке дважды. В результат ученик всё равно попал бы один раз: IN только проверяет, есть ли значение в списке.

Через JOIN ученик с двумя двойками появился бы дважды, и понадобился бы DISTINCT. Подзапрос с IN часто читается проще.


NOT IN — значения, которых нет в списке

По каким предметам ещё не ставили оценок?

SELECT name
FROM subjects
WHERE id NOT IN (SELECT subject_id FROM grades);

Результат:

name
История

Ловушка: NOT IN и NULL

Найдём классы, в которых нет учеников. Запрос выглядит правильным:

SELECT name
FROM classes
WHERE id NOT IN (SELECT class_id FROM students);

Результат: пустая таблица — ни одной строки.

Но класс 9А пустой, а в результате его нет! Причина — Лиза Орлова, у которой class_id равен NULL. В списке, который вернул подзапрос, есть NULL.

Проверим без таблиц. SELECT без FROM возвращает одну строку, если условие в WHERE истинно, и ни одной — если нет. Запрос SELECT 'да' WHERE 4 NOT IN (1, 2, 3); вернёт «да».

А 4 NOT IN (1, 2, 3, NULL) означает «4 ≠ 1 и 4 ≠ 2 и 4 ≠ 3 и 4 ≠ NULL».

Сравнение с NULL даёт не «истину», а «неизвестно» (NULL), и всё условие становится «неизвестно». Строка в результат не попадает.

В списке для NOT IN не должно быть NULL.

Исправление — убрать NULL из подзапроса:

SELECT name
FROM classes
WHERE id NOT IN (SELECT class_id FROM students WHERE class_id IS NOT NULL);

Результат:

name
9А

Другой надёжный способ — NOT EXISTS, о нём следующий урок. У IN без NOT такой проблемы нет: NULL в списке просто ни с чем не совпадает.


А как же ANY и ALL?

В PostgreSQL и других СУБД есть операторы > ALL (подзапрос) и > ANY (подзапрос). В SQLite их нет — будет синтаксическая ошибка. Их заменяют скалярным подзапросом с агрегатом:

В других СУБДВ SQLite
x > ALL (SELECT v …)x > (SELECT MAX(v) …)
x > ANY (SELECT v …)x > (SELECT MIN(v) …)
x = ANY (SELECT v …)x IN (SELECT v …)

Замена не во всём равносильна:

  • от пустого списка x > ALL истинно, а x > (SELECT MAX(v) …) — нет: MAX от пустого набора даёт NULL;
  • если среди значений есть NULL, x > ALL даёт «неизвестно», а MAX пропускает NULL и сравнивает с наибольшим из остальных.

Коротко

  • значение IN (подзапрос) — есть ли значение среди строк подзапроса. Подзапрос возвращает один столбец.
  • NOT IN — значения нет в списке. Если в списке есть NULL, результат всегда пустой: отфильтруйте NULL или используйте NOT EXISTS.
  • В SQLite нет ANY и ALL; вместо них — MIN, MAX и IN.

До сих пор подзапрос не зависел от внешнего запроса. А если ему нужно знать, какую строку сейчас проверяют?