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

Нет общего победителя. Современный оптимизатор часто преобразует эквивалентные формы в один план, но семантика и возможности конкретной СУБД важнее внешнего вида.

Когда JOIN выражает задачу

Нужны столбцы обеих таблиц:

SELECT o.id, u.email
FROM orders AS o
JOIN users AS u ON u.id = o.user_id;

Нужно помнить о кардинальности: несколько совпадений справа размножают строки слева.

Когда EXISTS выражает задачу

Нужно только проверить существование:

SELECT u.id, u.email
FROM users AS u
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.user_id = u.id
);

Это semi join: каждый пользователь возвращается не более одного раза без DISTINCT.

Когда скалярный подзапрос уместен

Нужно сравнение с одним вычисленным значением:

SELECT id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Предварительная агрегация

SELECT u.id, stats.amount
FROM users AS u
JOIN (
    SELECT user_id, SUM(total) AS amount
    FROM orders
    GROUP BY user_id
) AS stats ON stats.user_id = u.id;

Подзапрос закрепляет гранулярность «одна строка на пользователя» до join и предотвращает случайное размножение суммы.

Как выбирать

Какая кардинальность результата нужна?
Нужны ли столбцы правой стороны?
Что должно происходить при отсутствии строки и NULL?
Есть ли гарантия одной строки?
Каков фактический план на реальных данных?

Ловушки

NOT IN с NULL не эквивалентен NOT EXISTS.
JOIN + DISTINCT может скрывать неверную семантику.
Коррелированный подзапрос не обязательно медленный: оптимизатор может decorrelate его.
Переписывание в JOIN не гарантирует ускорение.

Связано: Запрос из 2х таблиц, Оператор EXISTS, Подзапросы с одной строкой и одним столбцом, SQL оценка производительности.

Вопросы

Что выбрать для проверки наличия? Обычно EXISTS.

Всегда ли JOIN быстрее? Нет, эквивалентные формы могут дать одинаковый план.

Почему JOIN размножает строки? Он возвращает каждую подходящую пару.

Когда полезен подзапрос с GROUP BY? Когда нужно зафиксировать гранулярность до соединения.

JavaNotes

Explorer

Что лучше подзапросы или JOIN