JavaNotes

Подзапросы

2 min read

Подзапросы

Подзапрос — выражение SELECT, вложенное в другой запрос. Он может вернуть одно значение, набор значений или таблицу. Оптимизатор не обязан исполнять его буквально «сначала внутри, потом снаружи»: запрос может быть преобразован в join или другой план.

Скалярный подзапрос

SELECT p.id, p.total
FROM purchase p
WHERE p.total > (
    SELECT AVG(p2.total)
    FROM purchase p2
);

В месте сравнения требуется не более одной строки и одного столбца. Если подзапрос вернёт несколько строк, СУБД сообщит об ошибке.

IN

SELECT c.id, c.name
FROM customer c
WHERE c.id IN (
    SELECT p.customer_id
    FROM purchase p
    WHERE p.status = 'PAID'
);

Подзапрос возвращает один столбец и любое количество строк. Для большого динамического набора план может быть похож на semi join.

EXISTS

SELECT c.id, c.name
FROM customer c
WHERE EXISTS (
    SELECT 1
    FROM purchase p
    WHERE p.customer_id = c.id
      AND p.status = 'PAID'
);

Это коррелированный подзапрос: он ссылается на текущую строку c. Семантически достаточно первого совпадения. SELECT 1 подчёркивает, что значения вложенного результата не используются.

Ловушка NOT IN и NULL

WHERE c.id NOT IN (SELECT p.customer_id FROM purchase p)

Если подзапрос содержит NULL, сравнение может стать UNKNOWN для всех строк и результат окажется пустым. Для антисоединения надёжнее:

WHERE NOT EXISTS (
    SELECT 1
    FROM purchase p
    WHERE p.customer_id = c.id
)

Либо нужно явно исключить NULL во вложенном запросе.

Подзапрос в FROM

SELECT x.customer_id, x.paid_total
FROM (
    SELECT p.customer_id, SUM(p.total) AS paid_total
    FROM purchase p
    WHERE p.status = 'PAID'
    GROUP BY p.customer_id
) AS x
WHERE x.paid_total >= 1000;

Так сначала задаётся гранулярность набора, а затем он соединяется или фильтруется. CTE через WITH часто делает ту же идею читаемее.

Подзапрос или JOIN

Выбор начинают с смысла:

  • нужны столбцы обеих таблиц — join;
  • нужен факт наличия — EXISTS;
  • нужен агрегат для сравнения — scalar/correlated subquery;
  • нужно предварительно изменить гранулярность — derived table или CTE.

Производительность сравнивают по фактическому плану. Современный оптимизатор может построить одинаковый план для разных записей одного запроса.

На собеседовании

Всегда ли коррелированный подзапрос выполняется по разу на строку? Логически он зависит от внешней строки, но оптимизатор может декоррелировать его. Смотрят execution plan.

Чем EXISTS отличается от IN? EXISTS выражает наличие совпадения; IN сравнивает с набором значений. Особенно важно различие NOT EXISTS и NOT IN при NULL.

Связанные заметки: Что лучше подзапросы или JOIN, Многотабличные запросы операция JOIN, SQL.

Graph View

Backlinks