SQL ·
‹ Предыдущий Следующий ›
⏱ 5 минут чтения Обновлено: 2026-11-20

Подзапросы в 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.

Комментарии

Зарегистрируйтесь или войдите, чтобы иметь возможность оставить комментарий.