Чем EXISTS отличается от IN и JOIN и когда PostgreSQL выполнит их одинаково?

ЗнаниеSeniorЧасто

Проверила команда СопроводОбновлено

Короткий ответ

JOIN возвращает строку на каждое совпадение и дает столбцы второй таблицы. EXISTS и IN с подзапросом только проверяют, есть ли пара, и возвращают строку один раз. PostgreSQL разворачивает EXISTS и IN в одно и то же полусоединение, и планы совпадают. Расходятся отрицания: NOT IN при NULL в подзапросе возвращает пустой результат, NOT EXISTS работает верно.

Почему этот вопрос задают

Интервьюер проверяет, знаете ли вы, как три формы ведут себя на повторах и NULL, и умеете ли подтвердить ответ планом вместо мифа «EXISTS всегда быстрее».

Как рассуждать

  1. Начните с результата: JOIN размножает строки, EXISTS и IN возвращают каждую строку один раз
  2. Объясните ловушку: NOT IN при одном NULL в подзапросе не вернет ни строки
  3. Покажите план: EXISTS и IN дают одно полусоединение, NOT IN остается подзапросом
  4. Выберите форму по задаче: нужны столбцы второй таблицы — JOIN, проверка наличия — EXISTS

Ответ с примером

JOIN возвращает строку на каждое совпадение: участник бонусной программы с пятью заправками попадет в результат пять раз, и для списка участников придется добавлять DISTINCT. EXISTS и IN с подзапросом отвечают на вопрос «есть ли хотя бы одна заправка» и возвращают участника один раз. Это полусоединение, и PostgreSQL разворачивает обе формы в одно и то же соединение: в примере планы совпадают, повторы member_id убирает HashAggregate, затем идет Hash Join. С отрицанием формы расходятся. NOT EXISTS превращается в антисоединение, NOT IN остается фильтром с подзапросом и при хотя бы одном NULL в member_id возвращает пустой результат: одна заправка без карты обнулит отчет.

SQL
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: в чем разница

СвойствоJOINEXISTSIN с подзапросом
Повторы строк первой таблицыпо строке на совпадениенетнет
Столбцы второй таблицыдоступнынетнет
ОтрицаниеLEFT JOIN и проверка IS NULLNOT 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?

Похожие вопросы

ЗадачаMiddleЧасто

Найдите клиентов, которые ни разу не сделали заказ

Проверьте для каждого клиента, что у него нет ни одного заказа: WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id). Тот же ответ даст LEFT JOIN с условием o.customer_id IS NULL. Вариант NOT IN (SELECT customer_id FROM orders) опасен: достаточно одного заказа с пустым customer_id, и запрос вернет ноль строк.

  • NOT EXISTS
  • JOIN
248
ЗнаниеJuniorЧасто

Какие виды JOIN бывают и чем LEFT JOIN отличается от INNER JOIN?

Основные виды: INNER, LEFT, RIGHT, FULL и CROSS JOIN. INNER JOIN оставляет только строки, у которых нашлась пара по условию ON. LEFT JOIN сохраняет все строки левой таблицы, а там, где пары нет, заполняет столбцы правой значением NULL. Поэтому LEFT JOIN берут, когда важны и записи без совпадений, например читатели без выдач.

  • JOIN
261
ЗнаниеMiddleЧасто

Как прочитать план EXPLAIN ANALYZE и найти в нем узел, на который уходит время?

План читают от вложенных узлов к верхнему: каждый узел получает строки от дочерних. Время в actual time включает время детей, поэтому собственное время узла — его время минус время дочерних узлов. Если узел запускался несколько раз, умножьте время на loops. Ищите узел с самым большим собственным временем и сверяйте оценку строк с фактом.

  • EXPLAIN
  • оптимизация запросов
263
ЗнаниеJuniorЧасто

Чем ROW_NUMBER отличается от RANK и DENSE_RANK?

Все три функции нумеруют строки окна в порядке из ORDER BY, а различаются на равных значениях. ROW_NUMBER дает каждой строке свой номер, даже при равенстве. RANK дает равным строкам одинаковый номер и пропускает следующие: 1, 2, 2, 4. DENSE_RANK тоже дает одинаковый номер, но без пропусков: 1, 2, 2, 3.

  • оконные функции
239

Частые вопросы по теме

Короткие ответы на частые вопросы по этой теме.

Для senior это сильный ход: скажите, что проверили бы EXPLAIN, и назовите узлы, которые ждете увидеть. Полусоединение в плане выглядит как Semi Join или как обычное соединение поверх HashAggregate, антисоединение — как Anti Join.

Создайте две таблицы, добавьте в подзапрос строку с NULL и сравните четыре запроса: JOIN, IN, NOT IN и NOT EXISTS. Посмотрите их результаты и планы через EXPLAIN.

На выбор формы нет, на план да: с индексом по fuelings.member_id планировщик может взять вложенный цикл с поиском по индексу. Обе формы и тогда получают одинаковый план.

Общие вопросы на интервью

Общий вопросС опытомЧасто

Почему вы хотите работать у нас?

Назовите две-три причины, которые связывают вас именно с этой компанией: продукт, задачи, масштаб, команда. Каждую подкрепите фактом, который нашли сами: новость, проект, текст вакансии, свой опыт клиента. Потом покажите, что принесете со своей стороны. Слова о стабильности и известном бренде подходят любой компании, поэтому на них ответ не держится.

  • мотивация
  • о компании
290
Общий вопросБез опытаЧасто

Какие у вас сильные стороны?

Хватит двух-трех сильных сторон, которые нужны на этой работе, если к каждой есть пример: что сделали и какой был результат. Берите качества из текста вакансии, а не из списка «ответственный, коммуникабельный». Без опыта примеры ищите в учебе, практике и подработке. Говорите спокойно, без лишней скромности и без хвастовства.

  • сильные стороны
  • без опыта
276
Общий вопросБез опытаЧасто

Кем вы видите себя через 5 лет?

Расскажите, какие задачи хотите решать через несколько лет и чему для этого научитесь. Свяжите план с тем, что дает эта компания, и покажите первый шаг, который уже сделали. Точную должность называть не обязательно. Важнее, чтобы план был реальным и вел через эту работу, а не мимо нее.

  • мотивация
  • карьерные планы
289
ИсторияС опытомЧасто

Расскажите о конфликте с коллегой: из-за чего он возник и как вы его решили?

Расскажите один реальный конфликт по рабочему вопросу: из-за чего разошлись, чего хотела каждая сторона и как вы договорились. Говорите спокойно, без оценок коллеги. Покажите, что вы искали решение, где-то уступили и сохранили рабочие отношения. Закончите результатом и тем, что теперь делаете, чтобы похожий спор не повторился.

  • конфликты
  • работа в команде
252
ИсторияС опытомЧасто

Каким профессиональным достижением вы гордитесь больше всего?

Назовите одно достижение, близкое к задачам новой работы, и расскажите его как историю: какая была проблема, что сделали лично вы и что изменилось в цифрах. Сильнее всего звучит результат, который держится и после вас: новый порядок работы, правило, схема. Если результат общий, прямо скажите, какая часть в нем ваша.

  • достижения
  • результат в цифрах
276
Общий вопросБез опытаЧасто

Опыта работы еще нет, а интервьюер просит рассказать о себе: что вы скажете?

Начните с того, кто вы сейчас и на какую работу пришли. Вместо стажа расскажите об учебе, практике и подработке, которые похожи на задачи вакансии, и назовите один результат с цифрой. Закончите тем, почему хотите начать карьеру именно здесь. Уложитесь в одну-две минуты и не пересказывайте резюме по порядку.

  • о себе
  • без опыта
244

Вопросы руководителю на интервью

ИсторияРуководительЧасто

Как вы делегируете задачи: что отдаете команде, а что оставляете себе?

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

  • делегирование
  • команда
255
КейсРуководительЧасто

Два сотрудника вашей команды месяц не разговаривают друг с другом, и общий отчет уже сорвал срок — что сделаете?

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

  • конфликт
  • ответственность
242
ИсторияРуководительЧасто

Расскажите о сотруднике, которого вы вырастили: каким он пришел и чего добился?

Возьмите одного человека и покажите путь: каким он пришел, какие задачи вы ему давали по нарастающей и чем помогали, кем он стал и за какой срок. Назовите цифру роста: новая должность, своя зона, люди в подчинении. Скажите честно, где он вырос сам, а где сработали ваши решения.

  • развитие сотрудников
  • наставничество
303
ИсторияРуководительЧасто

Расскажите о цели, которую ваша команда не выполнила: когда вы это поняли и что сделали?

Назовите цель с цифрой и честный итог. Главное в ответе — момент, когда вы поняли, что цель срывается, и по какой цифре это увидели. Дальше расскажите, кому и когда сказали, что пересобрали, чтобы спасти главное, и какой один вывод сделали для следующего плана.

  • цели
  • провал
241
Общий вопросРуководительЧасто

Как вы измеряете результат своей команды?

Назовите два-три показателя, которые отражают то, ради чего существует ваша команда, и их текущие цифры. Добавьте показатель качества, который не дает гнаться за одним числом. Скажите, как часто смотрите на цифры, кто их видит и какое решение вы приняли по ним в последний раз.

  • показатели
  • цели
216
ИсторияРуководительЧасто

Расскажите, как вы разложили цель компании на задачи для своей команды

Возьмите одну цель компании с цифрой и сроком и покажите, как вы ее разбили: на какие части, по какому признаку, какие личные цели получил каждый сотрудник. Расскажите, как следили за ходом и что меняли по дороге. Закончите итогом план-факт честно, даже если цель выполнена не целиком.

  • цели
  • планирование
227