Вопросы по теме «Подзапросы в SQL: скалярные, IN, EXISTS и коррелированные»
Сначала изучите урок: Подзапросы в SQL: скалярные, IN, EXISTS и коррелированные
Всего: 7 вопросов
1. Что такое подзапрос в SQL и где его можно использовать?
Что такое подзапрос в 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 и почему 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?
Что такое производная таблица и 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?
Если из второй таблицы нужны столбцы — 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?
Какие ошибки чаще всего допускают с подзапросами в 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: обернуть подзапрос в производную таблицу.