Подзапросы в SQL: скалярные, IN, EXISTS и коррелированные
Нужно найти пользователей форума, которые не написали ни одного сообщения. Запрос SELECT username FROM users WHERE id NOT IN (SELECT author_id FROM messages); выполняется без ошибок и возвращает Empty set, хотя Анна не писала ничего. Виновато одно сообщение, у которого автор не указан: в messages.author_id лежит NULL, и из-за него NOT IN отбрасывает все строки.
Подзапрос (вложенный запрос, subquery) — это команда SELECT, записанная в круглых скобках внутри другой SQL-команды. Внешний запрос использует результат подзапроса как значение, как список значений для IN, как проверку существования строк (EXISTS) или как временную таблицу в FROM. Подзапрос может быть независимым или коррелированным — ссылаться на столбцы внешнего запроса.
1. Что такое подзапрос и где его можно написать
Подзапрос можно поставить почти в любое место SQL-команды, где ожидается значение, список значений или таблица: в список столбцов SELECT, в FROM, в условия WHERE и HAVING, а также внутрь INSERT, UPDATE и DELETE. Что именно допустимо в каждом месте, зависит от формы результата подзапроса.
| Вид подзапроса | Что возвращает | Где стоит | Пример |
|---|---|---|---|
| Скалярный | Одну строку и один столбец | Список SELECT, WHERE/HAVING с =, >, <, SET в UPDATE | WHERE views > (SELECT AVG(views) FROM topics) |
| Столбцовый | Один столбец, сколько угодно строк | IN, NOT IN, ANY, ALL | WHERE id IN (SELECT author_id FROM topics) |
| Строковый (row subquery) | Одну или несколько строк из нескольких столбцов | Сравнение кортежей: (a, b) = (...), (a, b) IN (...) | WHERE (topic_id, id) IN (SELECT topic_id, MAX(id) FROM messages GROUP BY topic_id) |
| Табличный | Любой набор строк и столбцов | FROM (производная таблица), EXISTS, INSERT ... SELECT | FROM (SELECT ...) AS t |
Отдельно от формы результата подзапросы делят на независимые и коррелированные. Независимый подзапрос можно выполнить сам по себе: SELECT AVG(views) FROM topics вернёт одно число, сколько бы раз его ни вызывали. Коррелированный ссылается на строку внешнего запроса (WHERE m2.topic_id = m.topic_id) и вне его не выполняется — он разобран в разделе 7.
Вставка результата запроса в таблицу (INSERT ... SELECT) тоже использует вложенный SELECT, но разобрана в уроке про INSERT. Из Java такие запросы отправляют через JDBC так же, как любой другой SELECT, а в JPQL подзапросы допустимы в WHERE и HAVING.
2. Данные для примеров
Все примеры выполняются в MySQL 8 на учебной базе forum. Пересоздайте её скриптом из раздела Схема учебной базы форума, затем выполните блок ниже: он добавляет к темам столбцы created_at и views (как в уроке про DML), пользователя без тем и сообщений, третью тему и сообщение без автора.
ALTER TABLE topics
ADD COLUMN created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ADD COLUMN views INT NOT NULL DEFAULT 0;
INSERT INTO users (username, email) VALUES ('Anna', '[email protected]');
INSERT INTO topics (title, author_id) VALUES ('Зимняя рыбалка', 1);
INSERT INTO messages (topic_id, author_id, body) VALUES
(3, 1, 'Лёд уже держит?'),
(3, 3, 'Пока тонкий.'),
(2, NULL, 'Продаю гибрид, почти новый.');
UPDATE topics SET views = 120 WHERE id = 1;
UPDATE topics SET views = 45 WHERE id = 2;
UPDATE topics SET views = 30 WHERE id = 3; Сообщение 7 без автора — обычная ситуация для этой схемы: столбец messages.author_id допускает NULL, потому что при удалении пользователя внешний ключ с ON DELETE SET NULL обнуляет автора у его сообщений (подробнее — в уроке про ключи). Состояние таблиц после скрипта:
SELECT id, title, author_id, views FROM topics;
SELECT id, topic_id, author_id FROM messages; +----+----------------+-----------+-------+
| id | title | author_id | views |
+----+----------------+-----------+-------+
| 1 | О рыбалке | 1 | 120 |
| 2 | Велосипеды | 2 | 45 |
| 3 | Зимняя рыбалка | 1 | 30 |
+----+----------------+-----------+-------+
+----+----------+-----------+
| id | topic_id | author_id |
+----+----------+-----------+
| 1 | 1 | 1 |
| 2 | 1 | 3 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 1 |
| 6 | 3 | 3 |
| 7 | 2 | NULL |
+----+----------+-----------+ Пользователи: 1 Kirill, 2 Vasya, 3 Semen, 4 Anna. Теги темы 2 — отдых, спорт, покупки; у темы 1 один тег «отдых», у темы 3 тегов нет.
3. Скалярный подзапрос: одно значение
Скалярный подзапрос возвращает ровно одну строку с одним столбцом, и внешний запрос использует его как обычное значение: сравнивает с ним в WHERE или выводит в списке SELECT. Если подзапрос вернул больше одной строки, MySQL выдаёт ошибку 1242; если ни одной — результатом становится NULL.
Темы, которые просматривали чаще среднего. Среднее по трём темам — (120 + 45 + 30) / 3 = 65:
SELECT id, title, views
FROM topics
WHERE views > (SELECT AVG(views) FROM topics); +----+-----------+-------+
| id | title | views |
+----+-----------+-------+
| 1 | О рыбалке | 120 |
+----+-----------+-------+ Без подзапроса пришлось бы сначала выполнить SELECT AVG(views), запомнить 65 и подставить во второй запрос. Написать WHERE views > AVG(views) нельзя: агрегатная функция в WHERE даёт ошибку 1111 (почему — в уроке про HAVING).
В списке SELECT скалярный подзапрос вычисляется как ещё один столбец — например, отклонение от среднего:
SELECT title, views,
views - (SELECT AVG(views) FROM topics) AS diff
FROM topics
ORDER BY id; +----------------+-------+----------+
| title | views | diff |
+----------------+-------+----------+
| О рыбалке | 120 | 55.0000 |
| Велосипеды | 45 | -20.0000 |
| Зимняя рыбалка | 30 | -35.0000 |
+----------------+-------+----------+ AVG от целого столбца возвращает DECIMAL с четырьмя знаками после точки, поэтому и разность получилась дробной.
Ошибка 1242: Subquery returns more than 1 row
Найдём темы автора, который писал в теме 3:
SELECT title FROM topics
WHERE author_id = (SELECT author_id FROM messages WHERE topic_id = 3); ERROR 1242 (21000): Subquery returns more than 1 row В теме 3 писали двое — Кирилл и Семён, и сравнить одно число со списком через = нельзя. Коварство в том, что на других данных тот же запрос работает: пока в теме 3 было одно сообщение, ошибки не было. Исправлений три, и они дают разный смысл: IN (темы любого из авторов — следующий раздел), агрегат (SELECT MIN(author_id) ... — один конкретный автор) или ORDER BY ... LIMIT 1 (первый по заданному порядку). LIMIT 1 без ORDER BY убирает ошибку, но возвращает произвольную строку.
Пустой подзапрос даёт NULL
Если скалярный подзапрос не нашёл ни одной строки, ошибки нет — его значение равно NULL:
SELECT (SELECT id FROM users WHERE username = 'Petr') AS petr_id;
SELECT title FROM topics
WHERE author_id = (SELECT id FROM users WHERE username = 'Petr'); +---------+
| petr_id |
+---------+
| NULL |
+---------+
Empty set (0.00 sec) Второй запрос не сообщает, что пользователя Petr нет: author_id = NULL никогда не бывает истинным, и результат просто пуст. Опечатка в имени выглядит так же, как «у пользователя нет тем». Если нужно значение по умолчанию, подзапрос оборачивают в COALESCE((SELECT ...), 0).
4. Подзапрос с IN и NOT IN
значение IN (SELECT столбец ...) истинно, если значение совпадает хотя бы с одной строкой подзапроса. Подзапрос возвращает один столбец и любое число строк, а каждая строка внешнего запроса попадает в результат не больше одного раза, даже если совпадений несколько. NOT IN — обратная проверка, и на NULL в подзапросе она ломается.
Пользователи, которые писали в теме «Велосипеды»:
SELECT username
FROM users
WHERE id IN (SELECT author_id FROM messages WHERE topic_id = 2)
ORDER BY id; +----------+
| username |
+----------+
| Vasya |
| Semen |
+----------+ Подзапрос вернул 2, 3 и NULL (сообщение 7). NULL для IN не помеха: Вася и Семён нашлись, остальным совпадать не с чем. Исправленный запрос из раздела 3 — WHERE author_id IN (SELECT author_id FROM messages WHERE topic_id = 3) — вернёт обе темы Кирилла, «О рыбалке» и «Зимняя рыбалка»: у Семёна тем нет.
NOT IN и NULL: почему результат пустой
Вернёмся к запросу из начала урока:
SELECT username
FROM users
WHERE id NOT IN (SELECT author_id FROM messages); Empty set (0.00 sec) Подзапрос вернул 1, 3, 2, 3, 1, 3 и NULL. Для Анны условие раскрывается в 4 <> 1 AND 4 <> 3 AND ... AND 4 <> NULL; последнее сравнение даёт не истину, а NULL (UNKNOWN), и WHERE строку отбрасывает. Как это работает со списком констант, разобрано в уроке про SELECT; с подзапросом опаснее, потому что NULL приходит из данных и появляется в любой момент. Два надёжных исправления:
-- 1. Отфильтровать NULL в подзапросе
SELECT username FROM users
WHERE id NOT IN (SELECT author_id FROM messages WHERE author_id IS NOT NULL);
-- 2. NOT EXISTS: NULL на результат не влияет
SELECT username FROM users u
WHERE NOT EXISTS (SELECT 1 FROM messages m WHERE m.author_id = u.id); +----------+
| username |
+----------+
| Anna |
+----------+ Оба запроса возвращают Анну. Второй вариант лучше: он не требует помнить, может ли столбец подзапроса содержать NULL, и не сломается, если завтра столбец станет nullable.
Запрос работал месяцами, а потом перестал
Пока ни одного пользователя не удаляли, в messages.author_id нет NULL, и NOT IN (SELECT author_id FROM messages) отвечает правильно. Первое же удаление пользователя (ON DELETE SET NULL) превращает результат в пустой — без ошибки и без изменений в коде. Если столбец подзапроса допускает NULL, пишите NOT EXISTS.
MySQL не разрешает LIMIT внутри подзапроса для IN: WHERE id IN (SELECT author_id FROM topics ORDER BY views DESC LIMIT 2) завершится ошибкой ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'. Обход — обернуть подзапрос с LIMIT в производную таблицу (раздел 8): IN (SELECT author_id FROM (SELECT author_id FROM topics ORDER BY views DESC LIMIT 2) AS top).
5. ANY, SOME и ALL
Операторы ANY (синоним — SOME) и ALL сравнивают значение со всеми строками столбцового подзапроса через =, >, < и другие операторы сравнения. > ANY истинно, если условие выполняется хотя бы для одной строки, > ALL — если для всех. На практике их пишут редко: почти всегда понятнее IN, MIN или MAX.
| Запись | Чему равносильна | Подзапрос пустой |
|---|---|---|
x = ANY (...) | x IN (...) | Ложь |
x > ANY (...) | x > MIN(...), если в подзапросе нет NULL | Ложь |
x <> ALL (...) | x NOT IN (...), с той же проблемой NULL | Истина |
x > ALL (...) | x > MAX(...), но только на непустом подзапросе | Истина |
Темы, которые просматривали чаще любой темы Васи (у него одна тема, 45 просмотров):
SELECT title, views
FROM topics
WHERE views > ALL (SELECT views FROM topics WHERE author_id = 2); +-----------+-------+
| title | views |
+-----------+-------+
| О рыбалке | 120 |
+-----------+-------+ А если подставить Анну, у которой тем нет?
SELECT title FROM topics
WHERE views > ALL (SELECT views FROM topics WHERE author_id = 4);
SELECT title FROM topics
WHERE views > (SELECT MAX(views) FROM topics WHERE author_id = 4); +----------------+
| title |
+----------------+
| О рыбалке |
| Велосипеды |
| Зимняя рыбалка |
+----------------+
Empty set (0.00 sec) ALL и MAX расходятся на пустом наборе
«Больше всех элементов пустого множества» по правилам логики истинно для любого числа, поэтому > ALL вернул все три темы. MAX по пустому набору равен NULL, сравнение с ним не истинно, и второй запрос пуст. Заменяя одно на другое при рефакторинге, проверьте, может ли подзапрос ничего не вернуть.
6. EXISTS и NOT EXISTS
EXISTS (подзапрос) истинно, если подзапрос вернул хотя бы одну строку, и ложно, если не вернул ни одной; NOT EXISTS — наоборот. Значения столбцов подзапроса не проверяются, поэтому внутри принято писать SELECT 1. В отличие от IN и NOT IN, результат EXISTS всегда истина или ложь и никогда не NULL.
Почти всегда подзапрос в EXISTS коррелированный: он проверяет, есть ли связанные строки у текущей строки внешнего запроса. Пользователи, у которых есть хотя бы одна тема:
SELECT username
FROM users u
WHERE EXISTS (SELECT 1 FROM topics t WHERE t.author_id = u.id)
ORDER BY u.id; +----------+
| username |
+----------+
| Kirill |
| Vasya |
+----------+ У Кирилла две темы, но в результате он один раз. Такую операцию называют полу-соединением (semi-join): из внешней таблицы берутся строки, для которых нашлась хотя бы одна пара, а сами пары в результат не попадают. Обратная операция — анти-соединение (anti-join): NOT EXISTS оставляет строки без пары. Темы без тегов:
SELECT title
FROM topics t
WHERE NOT EXISTS (SELECT 1 FROM topic_tags tt WHERE tt.topic_id = t.id); +----------------+
| title |
+----------------+
| Зимняя рыбалка |
+----------------+ Тот же результат даёт LEFT JOIN ... WHERE tt.topic_id IS NULL — этот приём разобран в уроке про JOIN. NOT EXISTS короче и прямо говорит, что проверяется отсутствие.
Писать внутри SELECT 1, SELECT * или SELECT id — разницы нет: MySQL игнорирует список столбцов в EXISTS и не читает лишние данные. SELECT 1 — соглашение, которое сразу показывает читателю, что значения не важны.
7. Коррелированные подзапросы
Коррелированный подзапрос — подзапрос, который ссылается на столбцы внешнего запроса и поэтому логически выполняется заново для каждой строки внешнего запроса. Независимый подзапрос вычисляется один раз, а результат коррелированного зависит от текущей строки: для темы 1 он считает одно, для темы 2 — другое.
Число сообщений каждого пользователя — коррелированный скалярный подзапрос в списке SELECT:
SELECT u.username,
(SELECT COUNT(*) FROM messages m WHERE m.author_id = u.id) AS messages
FROM users u
ORDER BY u.id; +----------+----------+
| username | messages |
+----------+----------+
| Kirill | 2 |
| Vasya | 1 |
| Semen | 3 |
| Anna | 0 |
+----------+----------+ u.id внутри скобок — ссылка на внешний запрос. Для Анны подзапрос не находит строк, а COUNT(*) по пустому набору даёт 0, поэтому она в списке с нулём, а не пропала и не получила NULL.
Последнее сообщение в каждой теме
Классическая задача «последняя строка в каждой группе». В messages нет даты, поэтому последним считаем сообщение с наибольшим id: номера AUTO_INCREMENT растут по мере вставки.
SELECT m.topic_id, m.id, m.body
FROM messages m
WHERE m.id = (SELECT MAX(m2.id)
FROM messages m2
WHERE m2.topic_id = m.topic_id)
ORDER BY m.topic_id; +----------+----+-----------------------------+
| topic_id | id | body |
+----------+----+-----------------------------+
| 1 | 2 | На водохранилище, с утра. |
| 2 | 7 | Продаю гибрид, почти новый. |
| 3 | 6 | Пока тонкий. |
+----------+----+-----------------------------+ Одна таблица участвует в запросе дважды, поэтому у неё два псевдонима: m — строка внешнего запроса, m2 — строки подзапроса. Логически запрос проходит по всем семи сообщениям:
| m.id | m.topic_id | Подзапрос: MAX(id) в этой теме | m.id = результат? |
|---|---|---|---|
| 1 | 1 | 2 | Нет |
| 2 | 1 | 2 | Да |
| 3 | 2 | 7 | Нет |
| 4 | 2 | 7 | Нет |
| 5 | 3 | 6 | Нет |
| 6 | 3 | 6 | Да |
| 7 | 2 | 7 | Да |
Тот же результат даёт строковый подзапрос без корреляции: WHERE (m.topic_id, m.id) IN (SELECT topic_id, MAX(id) FROM messages GROUP BY topic_id) — так же решён вопрос «строка с максимумом в группе» в FAQ урока про GROUP BY. В MySQL 8.0+ для таких задач есть и оконная функция ROW_NUMBER().
Производительность: что делает оптимизатор MySQL 8
«Выполняется для каждой строки» описывает смысл запроса, а не обязательно план. Оптимизатор MySQL 8 переписывает многие подзапросы в соединения: IN и EXISTS в WHERE — в полу-соединение (для EXISTS — начиная с 8.0.16), NOT IN и NOT EXISTS — в анти-соединение (с 8.0.17). После такого переписывания запрос работает не медленнее аналогичного JOIN.
Коррелированный скалярный подзапрос в SELECT или в сравнении через =, как в двух примерах выше, обычно так и выполняется построчно. Для семи сообщений это незаметно; для миллиона строк всё решает индекс по столбцу связи: здесь m2.topic_id и m.author_id — внешние ключи, и MySQL уже создал по ним индексы, поэтому каждый вызов подзапроса читает несколько строк, а не всю таблицу. Проверить, что выбрал оптимизатор, можно командой EXPLAIN перед запросом: DEPENDENT SUBQUERY в столбце select_type означает построчное выполнение. Индексы и чтение плана — тема отдельного урока.
8. Подзапрос в FROM: производная таблица и WITH
Подзапрос в FROM называют производной таблицей (derived table): его результат становится временной таблицей, из которой внешний запрос выбирает, фильтрует и группирует строки, как из обычной. В MySQL у производной таблицы обязательно должен быть псевдоним. Типичная причина её использовать — посчитать агрегат от агрегата.
Сколько сообщений в среднем пишет автор? Написать AVG(COUNT(*)) нельзя — вложенные агрегаты дают ERROR 1111 (HY000): Invalid use of group function. Сначала считаем сообщения по авторам (GROUP BY), потом берём среднее от результата:
SELECT AVG(cnt) AS avg_messages
FROM (SELECT author_id, COUNT(*) AS cnt
FROM messages
WHERE author_id IS NOT NULL
GROUP BY author_id) AS per_author; +--------------+
| avg_messages |
+--------------+
| 2.0000 |
+--------------+ Производная таблица per_author содержит три строки: Кирилл 2, Вася 1, Семён 3, среднее — 2. Анна в неё не попала: у неё нет сообщений, а GROUP BY по messages не создаёт групп для отсутствующих авторов. Если считать среднее по всем четырём пользователям, получится 1,5 — какой вариант верный, зависит от вопроса, а не от SQL.
Без псевдонима запрос не выполнится:
SELECT AVG(cnt)
FROM (SELECT author_id, COUNT(*) AS cnt FROM messages GROUP BY author_id); ERROR 1248 (42000): Every derived table must have its own alias WITH: общее табличное выражение (CTE)
С MySQL 8.0 тот же подзапрос можно вынести вперёд и дать ему имя через WITH — это общее табличное выражение (Common Table Expression, CTE). Запрос читается сверху вниз, а именованный результат можно использовать несколько раз. Пользователи, которые написали больше сообщений, чем в среднем пишет автор:
WITH per_author AS (
SELECT author_id, COUNT(*) AS cnt
FROM messages
WHERE author_id IS NOT NULL
GROUP BY author_id
)
SELECT u.username, p.cnt
FROM per_author p
JOIN users u ON u.id = p.author_id
WHERE p.cnt > (SELECT AVG(cnt) FROM per_author); +----------+-----+
| username | cnt |
+----------+-----+
| Semen | 3 |
+----------+-----+ С производными таблицами пришлось бы дважды переписать один и тот же подзапрос: в FROM и внутри скалярного подзапроса со средним. CTE существует только в пределах одной команды. Рекурсивная форма WITH RECURSIVE обходит иерархии (дерево категорий, цепочку руководителей), но это отдельная тема. В MySQL 5.7 WITH нет — там остаются только производные таблицы.
9. Подзапрос или JOIN
Если из второй таблицы нужны столбцы, используйте JOIN; если нужно только проверить, есть ли связанные строки, — EXISTS или IN. Многие задачи решаются обоими способами, и MySQL 8 часто строит для них одинаковый план, но результат может отличаться количеством строк: JOIN повторяет строку для каждой пары, а подзапрос в WHERE — нет.
SELECT username FROM users
WHERE id IN (SELECT author_id FROM topics)
ORDER BY id;
SELECT u.username
FROM users u
JOIN topics t ON t.author_id = u.id
ORDER BY t.id; +----------+
| username |
+----------+
| Kirill |
| Vasya |
+----------+
+----------+
| username |
+----------+
| Kirill |
| Vasya |
| Kirill |
+----------+ У Кирилла две темы, и JOIN вернул его дважды. Чтобы получить тот же ответ, что у IN, к JOIN добавляют DISTINCT — а это лишняя работа по поиску дубликатов. Виды соединений разобраны в уроке SQL: все виды JOIN и UNION.
| Задача | Подзапрос | JOIN | Что выбрать |
|---|---|---|---|
| Вывести тему и имя автора | Скалярный подзапрос в SELECT на каждую строку | JOIN users | JOIN: нужны столбцы обеих таблиц |
| Пользователи, у которых есть темы | EXISTS / IN, каждый пользователь один раз | JOIN + DISTINCT | Подзапрос: проверка существования без дубликатов |
| Пользователи без сообщений | NOT EXISTS | LEFT JOIN ... IS NULL | Оба работают; NOT IN — только если в подзапросе нет NULL |
| Сравнение со средним или максимумом | Скалярный подзапрос | JOIN с производной таблицей | Подзапрос короче |
| Агрегат от агрегата, повторное использование результата | Производная таблица или WITH | — | WITH, если результат нужен дважды |
| Число связанных строк для каждой строки | Коррелированный COUNT(*), 0 для строк без пары | LEFT JOIN + GROUP BY + COUNT(столбец) | На больших таблицах обычно быстрее JOIN с группировкой; проверяйте EXPLAIN |
10. Подзапросы в UPDATE и DELETE: ошибка 1093
В WHERE команд UPDATE и DELETE подзапросы пишут так же, как в SELECT. Ограничение MySQL одно: подзапрос не может читать ту же таблицу, которую команда изменяет, — такая команда завершается ошибкой 1093. Обходят его через производную таблицу, которую MySQL материализует заранее, или через UPDATE ... JOIN.
Подзапрос к другой таблице работает без ограничений — снимем тег «покупки» со всех тем:
DELETE FROM topic_tags
WHERE tag_id IN (SELECT id FROM tags WHERE name = 'покупки'); Query OK, 1 row affected (0.01 sec) Теперь поднимем просмотры темам, у которых их меньше среднего:
UPDATE topics
SET views = views + 10
WHERE views < (SELECT AVG(views) FROM topics); ERROR 1093 (HY000): You can't specify target table 'topics' for update in FROM clause MySQL запрещает читать изменяемую таблицу в подзапросе: иначе результат зависел бы от того, сколько строк уже обновлено к моменту очередного вычисления среднего. В PostgreSQL и Oracle такая команда допустима — подзапрос там видит данные до начала изменения. В MySQL подзапрос оборачивают ещё в один SELECT, чтобы получилась производная таблица:
UPDATE topics
SET views = views + 10
WHERE views < (SELECT avg_views
FROM (SELECT AVG(views) AS avg_views FROM topics) AS a); Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0 Среднее 65 вычислено один раз до изменения, и обновились темы 2 и 3: у них стало 55 и 40 просмотров. Тот же результат даёт многотабличный UPDATE с производной таблицей в JOIN (выполнять его вслед за предыдущей командой не нужно — просмотры вырастут ещё раз):
UPDATE topics t
JOIN (SELECT AVG(views) AS avg_views FROM topics) AS a
ON t.views < a.avg_views
SET t.views = t.views + 10; Как устроены UPDATE с JOIN и режим safe updates, разобрано в уроке про UPDATE.
Обёртка помогает, только если таблица материализуется
Обход работает, потому что производная таблица с агрегатом, DISTINCT, GROUP BY или LIMIT вычисляется во временную таблицу до начала изменения. Простую обёртку вроде (SELECT id FROM topics WHERE views < 50) AS x оптимизатор MySQL 8 может слить с внешним запросом (derived_merge) — и ошибка 1093 вернётся. Запретить слияние можно подсказкой /*+ NO_MERGE(x) */ после UPDATE или DELETE.
11. На чём чаще всего ошибаются с подзапросами
Самые неприятные ошибки с подзапросами не дают сообщения об ошибке: NOT IN с NULL, пустой скалярный подзапрос и неуточнённое имя столбца возвращают правдоподобный, но неверный результат. Остальные MySQL отклоняет с понятным кодом — они собраны в таблице вместе с исправлениями.
| Ситуация | Что выдаст MySQL 8 | Как исправить |
|---|---|---|
NOT IN (SELECT ...), в подзапросе есть NULL | Empty set без ошибки | NOT EXISTS или WHERE столбец IS NOT NULL в подзапросе |
| Скалярный подзапрос вернул несколько строк | ERROR 1242: Subquery returns more than 1 row | IN, агрегат (MAX, MIN) или ORDER BY ... LIMIT 1 |
| Скалярный подзапрос не нашёл строк | NULL: сравнение не истинно, строки молча пропадают | COALESCE((SELECT ...), значение) или проверка данных |
| Производная таблица без псевдонима | ERROR 1248: Every derived table must have its own alias | FROM (SELECT ...) AS t |
Подзапрос читает таблицу, которую меняет UPDATE/DELETE | ERROR 1093: You can't specify target table ... for update in FROM clause | Материализуемая производная таблица или UPDATE ... JOIN |
LIMIT внутри IN/ANY/ALL | ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' | Обернуть подзапрос с LIMIT в производную таблицу |
AVG(COUNT(*)) вместо подзапроса | ERROR 1111: Invalid use of group function | Производная таблица или WITH |
| Столбец в подзапросе без имени таблицы | Если в таблице подзапроса такого столбца нет, MySQL берёт его из внешнего запроса — без ошибки | Всегда уточнять столбцы псевдонимом: tt.topic_id |
Что выведет этот запрос?
Нужны сообщения из тем с тегом «спорт». Таблицу перепутали: вместо связи topic_tags в подзапросе указали tags, где столбца topic_id нет.
SELECT id, topic_id
FROM messages
WHERE topic_id IN (SELECT topic_id FROM tags WHERE name = 'спорт')
ORDER BY id; +----+----------+
| id | topic_id |
+----+----------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 2 |
+----+----------+ Ошибки нет, вернулись все семь сообщений. Не найдя topic_id в tags, MySQL по правилам SQL взял столбец из внешнего запроса — messages.topic_id. Подзапрос стал коррелированным: для каждого сообщения он возвращает его собственный topic_id (один раз, потому что тег «спорт» один), и условие topic_id IN (topic_id) истинно всегда. С псевдонимом ошибка видна сразу:
SELECT id, topic_id
FROM messages
WHERE topic_id IN (SELECT g.topic_id FROM tags g WHERE g.name = 'спорт'); ERROR 1054 (42S22): Unknown column 'g.topic_id' in 'field list' Правильный запрос идёт через таблицу связи и возвращает три сообщения темы «Велосипеды»:
SELECT id, topic_id
FROM messages
WHERE topic_id IN (SELECT tt.topic_id
FROM topic_tags tt
JOIN tags g ON g.id = tt.tag_id
WHERE g.name = 'спорт')
ORDER BY id; +----+----------+
| id | topic_id |
+----+----------+
| 3 | 2 |
| 4 | 2 |
| 7 | 2 |
+----+----------+ Часто задаваемые вопросы
Правда ли, что подзапросы всегда медленнее JOIN?
Нет, это совет времён MySQL 5.5, где IN (SELECT ...) часто выполнялся заново для каждой строки внешней таблицы. Начиная с MySQL 5.6 оптимизатор превращает IN в полу-соединение, с 8.0.16 — и EXISTS, а с 8.0.17 NOT IN и NOT EXISTS выполняются как анти-соединение. Медленными остаются коррелированные скалярные подзапросы на больших таблицах без индекса по столбцу связи. Сравнивать варианты нужно по EXPLAIN, а не по правилу.
Можно ли писать подзапросы в JPQL и Spring Data JPA?
Да. Спецификация JPQL разрешает подзапросы в WHERE и HAVING: с IN, EXISTS, ALL, ANY/SOME и как скалярное значение для сравнения. Подзапрос пишут по сущностям, а не по таблицам: select u from User u where exists (select 1 from Topic t where t.author = u). HQL в Hibernate 6 допускает подзапросы и в SELECT, и в FROM. В Spring Data такой запрос задают в @Query.
Можно ли использовать ORDER BY и LIMIT внутри подзапроса?
ORDER BY без LIMIT в подзапросе бессмыслен: порядок строк производной таблицы или списка для IN не сохраняется, и оптимизатор может его отбросить. Сортируют результат внешнего запроса. ORDER BY ... LIMIT работает в скалярном подзапросе и в производной таблице, а внутри IN, ANY и ALL MySQL выдаёт ошибку 1235 — обход описан в разделе IN и NOT IN.
Чем CTE отличается от представления и временной таблицы?
CTE (WITH) существует только внутри одной команды и нигде не хранится. Представление (CREATE VIEW) — сохранённый в базе запрос, им пользуются все сессии, пока его не удалят. Временная таблица (CREATE TEMPORARY TABLE) хранит данные до конца сессии, и её можно наполнить один раз, а читать во многих запросах. Для разовой разбивки сложного запроса на шаги подходит CTE.
Что выбрать: IN или EXISTS?
Для положительной проверки результат одинаковый, и MySQL 8 обычно выполняет оба как полу-соединение: IN удобен со списком значений из независимого подзапроса, EXISTS — когда условие связи сложнее одного столбца. Для отрицания выбирайте NOT EXISTS: NOT IN возвращает пустой результат, если в подзапросе есть NULL. Пример — в разделе NOT IN и NULL.
Комментарии