Будут ли строки с NULL в JOIN по ID соединяться

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

Знаете ли вы трёхзначную логику SQL: сравнение с NULL даёт не «ложь», а «неизвестно». Для дата-инженера и аналитика это вопрос о потерянных строках в витрине, поэтому следом спрашивают, как всё-таки соединить NULL с NULL и как это ведёт себя в Spark и pandas.

Как отвечать

Нет, не соединятся. NULL = NULL возвращает UNKNOWN, а условие ON пропускает только TRUE. Дальше всё зависит от типа соединения:

• INNER JOIN: строки с NULL в ключе пропадают с обеих сторон;
• LEFT JOIN: строка слева с NULL в ключе останется, но колонки правой таблицы в ней будут пустыми, даже если справа тоже есть строка с NULL;
• FULL JOIN: такие строки попадут в результат с обеих сторон, но каждая отдельно, без пары;
• как соединить явно: a.id IS NOT DISTINCT FROM b.id в PostgreSQL и по стандарту, <=> в MySQL и Spark SQL, eqNullSafe в PySpark.

Контраст, который стоит упомянуть: GROUP BY и DISTINCT считают все NULL одной группой, а JOIN нет. У pandas поведение обратное: merge соединяет NaN с NaN, поэтому один и тот же пайплайн на SQL и на pandas даёт разное число строк.

Практическая сторона: в Spark при внешнем соединении строки с NULL в ключе собираются в одной партиции и дают перекос. Если они в соединении не нужны, их отфильтровывают до JOIN.

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

Лечат через COALESCE(a.id, -1) = COALESCE(b.id, -1). Это ломает использование индекса и склеивает NULL с настоящими значениями -1, если такие появятся.

Путают пустое значение с отсутствием пары. NULL в колонках правой таблицы после LEFT JOIN бывает по двум причинам: пары не нашлось или в найденной строке значение пустое. Отличить их можно только по ключу b.id или другой колонке с NOT NULL.

Фильтруют правую таблицу в WHERE после LEFT JOIN: WHERE b.status = 'active' выкидывает строки без пары, и соединение молча превращается во внутреннее. Условие по правой таблице переносят в ON.