Про NULL, 0 и пустоту в SQL

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

Для начала, в чем различие между пустотой, NULL и 0? Разберемся на примерах.

NULL - это отсутствие информации как таковой, состояние неизвестности, и отражает состояние строки, а не конкретные данные. Например, если при заключении договора с клиентом мы не спросили его номер телефона, то тогда в БД должен отразиться NULL.

Пустота - это уже наличие информации о том, что значение известно и равно пустой строке. Продолжая аналогию с клиентом, это то же самое, что если бы мы спросили его номер телефона, а он отказался бы его называть (или бы нажал кнопку "пропустить" на сайте).

Ноль это обычное число, которое отражает количество, еще участвует в математических операциях. Но есть еще специфические применения 0, когда он выступает не как число, а как маркер отсутствия / отрицания. Одно из таких применений - это поля с флагом, где условно 1 - это да, а 0 - это нет.

Почему так важно разделять это всё? Что изменится, если, к примеру, мы везде будем записывать только пустоту или только NULL? В этом случае нарушится логика и сегментация. Возвращаясь к примеру, мы не сможем сказать, почему у нас отсутствует номер телефона клиента: потому что мы его не спросили или потому что он сам отказался его оставлять. Очевидно, что такие вещи важно различать.

Но ещё не всё так просто. Важно упомянуть тот факт, что очень часто в столбцах не может храниться одновременно и NULL, и пустота, и 0. Например, если у нас поле с датой (дата первого платежа по кредиту), то оно может принимать только значения дат или NULL. Но NULL в этом случае - не неизвестность, мы можем знать, что первого платежа еще не было, но из-за особенностей формата вместо пустоты ставим NULL. В таких ситуациях используются дополнительные столбцы-флаги со значениями да/нет, чтобы с их помощью различать неизвестность и просто пустое значение.

Теперь о том, как разные операторы обрабатывают пустоты, NULL и 0:

COUNT. Здесь важно различать COUNT(поле) и COUNT(*). COUNT(*) считает количество строк, независимо от того, есть в них NULL или нет. COUNT(поле) работает уже как агрегатная функция и выдает кол-во именно известных значений, т.е. NULL игнорируется.

AVG. Считает среднее, игнорируя NULL и учитывая 0, что, впрочем, логично.

MIN и MAX точно так же игнорируют NULL и учитывают 0. Но если использовать на числовом столбце ORDER BY, то NULL будет выводиться в самом верху, если сортировать по убыванию.

При сравнении чего-то с NULL (обычно в WHERE) с помощью =, <, > всегда будет выдаваться NULL, поэтому фильтровать нужно, используя IS NULL. При этом пустоту и 0 операторы сравнения поддерживают.

GROUP BY и PARTITION BY объединяет всё по разным группам: все NULL - в одну группу, пустоту - в другую, 0 - в третью.

DISTINCT считает все NULL дубликатами, поэтому оставляет только одно значение NULL. Аналогично с пустотой и 0.

UNION действует по такой же логике - убирает все дубли. Но UNION ALL объединяет всё как есть, ничего не убирая.

JOIN не присоединяет NULL к NULL, потому что NULL = NULL это не TRUE. Но соединяет пустоты и нули.

Ранжирующие функции (ROW_NUMBER(), RANK(), DENSE_RANK()) учитывают NULL и присваивают ранг.

Еще больше полезного материала про анализ данных, Python, SQL, базы данных и различные инструменты - в моем телеграм-канале Data analysis | Анализ данных | DA. Подписывайся!