Чем EXISTS отличается от IN и JOIN и когда PostgreSQL выполнит их одинаково?
Проверила команда Сопровод · Обновлено
Короткий ответ
JOIN возвращает строку на каждое совпадение и дает столбцы второй таблицы. EXISTS и IN с подзапросом только проверяют, есть ли пара, и возвращают строку один раз. PostgreSQL разворачивает EXISTS и IN в одно и то же полусоединение, и планы совпадают. Расходятся отрицания: NOT IN при NULL в подзапросе возвращает пустой результат, NOT EXISTS работает верно.
Почему этот вопрос задают
Интервьюер проверяет, знаете ли вы, как три формы ведут себя на повторах и NULL, и умеете ли подтвердить ответ планом вместо мифа «EXISTS всегда быстрее».
Как рассуждать
- Начните с результата:
JOINразмножает строки,EXISTSиINвозвращают каждую строку один раз - Объясните ловушку:
NOT INпри одномNULLв подзапросе не вернет ни строки - Покажите план:
EXISTSиINдают одно полусоединение,NOT INостается подзапросом - Выберите форму по задаче: нужны столбцы второй таблицы —
JOIN, проверка наличия —EXISTS
Ответ с примером
JOIN возвращает строку на каждое совпадение: участник бонусной программы с пятью заправками попадет в результат пять раз, и для списка участников придется добавлять DISTINCT. EXISTS и IN с подзапросом отвечают на вопрос «есть ли хотя бы одна заправка» и возвращают участника один раз. Это полусоединение, и PostgreSQL разворачивает обе формы в одно и то же соединение: в примере планы совпадают, повторы member_id убирает HashAggregate, затем идет Hash Join. С отрицанием формы расходятся. NOT EXISTS превращается в антисоединение, NOT IN остается фильтром с подзапросом и при хотя бы одном NULL в member_id возвращает пустой результат: одна заправка без карты обнулит отчет.
EXPLAIN (COSTS OFF)
SELECT name FROM members AS m
WHERE EXISTS (SELECT 1 FROM fuelings AS f WHERE f.member_id = m.id);
EXPLAIN (COSTS OFF)
SELECT name FROM members AS m
WHERE m.id IN (SELECT f.member_id FROM fuelings AS f); QUERY PLAN
------------------------------------------
Hash Join
Hash Cond: (m.id = f.member_id)
-> Seq Scan on members m
-> Hash
-> HashAggregate
Group Key: f.member_id
-> Seq Scan on fuelings f
(7 rows)
QUERY PLAN
------------------------------------------
Hash Join
Hash Cond: (m.id = f.member_id)
-> Seq Scan on members m
-> Hash
-> HashAggregate
Group Key: f.member_id
-> Seq Scan on fuelings f
(7 rows)EXISTS, IN и JOIN: в чем разница
| Свойство | JOIN | EXISTS | IN с подзапросом |
|---|---|---|---|
| Повторы строк первой таблицы | по строке на совпадение | нет | нет |
| Столбцы второй таблицы | доступны | нет | нет |
| Отрицание | LEFT JOIN и проверка IS NULL | NOT EXISTS, NULL не мешает | NOT IN, один NULL дает пустой результат |
| План в PostgreSQL | соединение | полусоединение | то же полусоединение |
Как ответить сильнее
Что добавит опытный кандидат
Что спросят следом
Доступно по подписке
Приемы опытных и готовые ответы
Частые ошибки
«
EXISTSвсегда быстрееIN»Скажите, что для подзапроса PostgreSQL строит одинаковое полусоединение. Покажите это через
EXPLAINи оставьтеEXISTSтам, где он читается понятнее«
NOT INиNOT EXISTS— одно и то же»Объясните разницу на
NULL: одинNULLв подзапросе делаетNOT INпустым, поэтому для такого столбца беритеNOT EXISTS«Дубли после
JOINуберу черезDISTINCT»Предупредите, что
DISTINCTпо неуникальным столбцам склеит разных людей с одним именем. Если столбцы второй таблицы не нужны, проверьте наличие черезEXISTS«
SELECT 1внутриEXISTSработает быстрее, чемSELECT *»Уточните, что столбцы внутри
EXISTSне вычисляются: результат зависит только от того, нашлась ли строка. Обе записи дают одинаковый план
Сигналы для интервьюера
Сильный ответ
Объясняет разницу через повторы и NULL, показывает одинаковый план EXISTS и IN и сам предупреждает про NOT IN
Тревожный сигнал
Верно объясняет повторы и NULL, но не может сказать, какие узлы плана ждет для EXISTS и NOT EXISTS
Красный флаг
Пишет NOT IN по столбцу с NULL и не может объяснить, почему запрос вернул пустой результат
Как еще могут спросить
- Что выбрать для проверки наличия связанных строк:
EXISTSилиIN? - Почему
NOT INс подзапросом может вернуть пустой результат? - Что такое полусоединение и антисоединение в PostgreSQL?
- Когда
JOINможно заменить наEXISTS?











