Вопросы по теме «Подзапросы в SQL: скалярные, IN, EXISTS и коррелированные»

Всего: 7 вопросов

1. 

Что такое подзапрос в SQL и где его можно использовать?

Подзапрос (вложенный запрос, subquery) — команда SELECT в круглых скобках внутри другой SQL-команды. Его пишут в списке SELECT, в FROM, в WHERE и HAVING, внутри INSERT, UPDATE и DELETE. Что допустимо в каждом месте, зависит от формы результата:

  • скалярный — одна строка и один столбец, используется как значение: WHERE views > (SELECT AVG(views) FROM topics);
  • столбцовый — один столбец, любое число строк: IN, NOT IN, ANY, ALL;
  • строковый — несколько столбцов для сравнения кортежей: (topic_id, id) IN (SELECT ...);
  • табличный — любой набор строк: производная таблица в FROM, EXISTS.

Независимый подзапрос можно выполнить сам по себе. Коррелированный ссылается на столбцы внешнего запроса (m2.topic_id = m.topic_id) и вне его не выполняется.

2. 

Что такое скалярный подзапрос и что будет, если он вернёт несколько строк или ни одной?

Скалярный подзапрос возвращает одну строку с одним столбцом, и внешний запрос использует его как обычное значение — в сравнении или как столбец в SELECT: views - (SELECT AVG(views) FROM topics) AS diff.

  • Несколько строк — ERROR 1242: Subquery returns more than 1 row. Ошибка зависит от данных: запрос работает, пока подходящая строка одна. Исправления дают разный смысл: IN (любое из значений), агрегат MIN/MAX (одно конкретное) или ORDER BY ... LIMIT 1 (первое по порядку). LIMIT 1 без ORDER BY вернёт произвольную строку.
  • Ни одной строки — ошибки нет, значение равно NULL. Условие author_id = NULL не бывает истинным, и результат молча пуст: опечатка в имени пользователя выглядит как «у него нет тем». Значение по умолчанию задают через COALESCE((SELECT ...), 0).
3. 

Как работают EXISTS и NOT EXISTS и почему NOT EXISTS надёжнее NOT IN?

EXISTS (подзапрос) истинно, если подзапрос вернул хотя бы одну строку; NOT EXISTS — если не вернул ни одной. Значения столбцов не проверяются, поэтому внутри принято писать SELECT 1. Подзапрос обычно коррелированный:

SELECT username FROM users u
WHERE NOT EXISTS (SELECT 1 FROM messages m WHERE m.author_id = u.id);

EXISTS — полу-соединение (semi-join): строка внешней таблицы попадает в результат один раз, сколько бы пар ни нашлось. NOT EXISTS — анти-соединение (anti-join), тот же результат даёт LEFT JOIN ... IS NULL.

Результат EXISTS — всегда истина или ложь, никогда не NULL. Поэтому запрос выше находит Anna, а NOT IN (SELECT author_id FROM messages) возвращает Empty set, как только в author_id появляется NULL — например, после удаления пользователя при ON DELETE SET NULL.

4. 

Что такое коррелированный подзапрос и как он выполняется?

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

SELECT u.username,
       (SELECT COUNT(*) FROM messages m WHERE m.author_id = u.id) AS messages
FROM users u;

Для Anna подзапрос не находит строк, а COUNT(*) по пустому набору равен 0 — она остаётся в результате с нулём. Если одна таблица нужна и снаружи, и внутри, ей дают два псевдонима (m и m2).

«Для каждой строки» — смысл запроса, а не обязательно план: MySQL 8 переписывает IN и EXISTS в полу-соединение, NOT IN и NOT EXISTS — в анти-соединение. Коррелированный скалярный подзапрос обычно выполняется построчно, и на больших таблицах всё решает индекс по столбцу связи. DEPENDENT SUBQUERY в выводе EXPLAIN означает построчное выполнение.

5. 

Что такое производная таблица и WITH (CTE) в SQL?

Производная таблица (derived table) — подзапрос в FROM: внешний запрос выбирает, фильтрует и группирует его результат, как обычную таблицу. Типичная задача — агрегат от агрегата. AVG(COUNT(*)) даёт ERROR 1111, поэтому сначала считают сообщения по авторам, потом среднее:

SELECT AVG(cnt) FROM (SELECT author_id, COUNT(*) AS cnt
  FROM messages WHERE author_id IS NOT NULL
  GROUP BY author_id) AS per_author;

В MySQL псевдоним обязателен: без AS per_author — ERROR 1248: Every derived table must have its own alias.

WITH — общее табличное выражение (CTE, с MySQL 8.0): подзапрос выносят вперёд и дают ему имя, WITH per_author AS (SELECT ...) SELECT .... Запрос читается сверху вниз, а результат можно использовать несколько раз, например в FROM и в подзапросе со средним. CTE существует только в пределах одной команды; в MySQL 5.7 WITH нет.

6. 

Когда использовать подзапрос, а когда JOIN?

Если из второй таблицы нужны столбцы — JOIN; если нужно только проверить, есть ли связанные строки, — EXISTS или IN.

Главное различие — число строк: JOIN повторяет строку для каждой пары, подзапрос в WHERE — нет. У Kirill две темы, поэтому WHERE id IN (SELECT author_id FROM topics) возвращает Kirill и Vasya, а JOIN topics — Kirill, Vasya, Kirill. Чтобы убрать дубликат, к JOIN пришлось бы добавить DISTINCT.

  • Сравнение со средним или максимумом — скалярный подзапрос короче.
  • Строки без пары — NOT EXISTS или LEFT JOIN ... IS NULL; NOT IN — только если в подзапросе нет NULL.
  • Агрегат от агрегата и повторное использование результата — производная таблица или WITH.

Совет «подзапросы всегда медленнее JOIN» устарел: с MySQL 5.6 IN выполняется как полу-соединение. Сравнивайте варианты по EXPLAIN.

7. 

Какие ошибки чаще всего допускают с подзапросами в SQL?

Опаснее всего ошибки без сообщения об ошибке:

  • NOT IN с NULL в подзапросе — Empty set. Исправление: NOT EXISTS или IS NOT NULL в подзапросе.
  • Пустой скалярный подзапрос — NULL, строки молча пропадают. Исправление: COALESCE или проверка данных.
  • Столбец без псевдонима — если в таблице подзапроса его нет, MySQL берёт столбец внешнего запроса, и подзапрос молча становится коррелированным. Исправление: tt.topic_id.

Остальные MySQL отклоняет с кодом:

  • ERROR 1242 — скалярный подзапрос вернул несколько строк: IN, агрегат или ORDER BY ... LIMIT 1;
  • ERROR 1248 — производная таблица без псевдонима;
  • ERROR 1093 — UPDATE/DELETE читает изменяемую таблицу в подзапросе: материализуемая производная таблица или UPDATE ... JOIN;
  • ERROR 1235 — LIMIT внутри IN/ANY/ALL: обернуть подзапрос в производную таблицу.
Страница 1 из 1