Как получить значения из одной таблицы, которых нет в другой в SQL

Что проверяют

Знаете ли вы несколько способов сделать anti join и, главное, чем они различаются на NULL и на больших объёмах. Почти всегда следом спрашивают, что будет с NOT IN, если в подзапросе окажется NULL, и как то же сделать в Spark.

Как отвечать

Четыре способа, у каждого своё поведение:

• NOT EXISTS: WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id). Корректен при NULL, работает с составными ключами, планировщик превращает его в anti join. Вариант по умолчанию;
• LEFT JOIN ... WHERE b.id IS NULL: тот же результат, если проверять колонку, которая в b объявлена NOT NULL, обычно ключ;
• NOT IN: читается проще всех, но если подзапрос вернёт хотя бы один NULL, условие для каждой строки станет UNKNOWN, и выборка окажется пустой. К тому же PostgreSQL в общем случае не превращает его в anti join, и на большом подзапросе, который не влез в work_mem, план деградирует;
• EXCEPT: сравнивает строки целиком, а не по ключу, и убирает дубликаты. Хорош для сверки двух наборов колонок.

В дата-инженерии тот же приём живёт и за пределами базы:

• Spark: df_a.join(df_b, "id", "left_anti") или LEFT ANTI JOIN в Spark SQL. Если df_b маленький, добавьте broadcast;
• pandas: a[~a["id"].isin(b["id"])] или merge с indicator=True и фильтром по left_only;
• ClickHouse: тоже есть LEFT ANTI JOIN.

Результат стоит подкрепить EXPLAIN: в хорошем плане видно Hash Anti Join или Merge Anti Join.

Где спотыкаются

Пишут NOT IN по колонке, которая допускает NULL, и получают пустую выборку без единой ошибки. Такой баг в отчёте могут не замечать неделями.

В варианте с LEFT JOIN проверяют на IS NULL колонку, которая бывает пустой и в найденных строках, или добавляют условие на правую таблицу в WHERE вместо ON. Результат искажается.

Повторяют старый миф, что подзапрос всегда медленнее соединения. Для NOT EXISTS это давно не так, план у него обычно тот же, что у LEFT JOIN, а проблемы с производительностью бывают у NOT IN.