Если вы пишете SQL-запросы, то наверняка сталкивались с ситуацией, когда данные исчезают, отчеты не сходятся, а бизнес теряет деньги. И виновник этого — маленькое, но очень коварное слово NULL. В 1974 году Эдгар Кодд, создатель реляционной модели данных, ввел это понятие, чтобы обозначить отсутствие информации. Он хотел, как лучше, но спустя пятьдесят лет NULL продолжает «терроризировать» разработчиков по всему миру. Важно усвоить раз и навсегда: NULL — это не значение. Это состояние неизвестности. Поэтому:

·       NULL ≠ 0 (ноль - это число);

·       NULL ≠ '' (пустая строка - это строка);

·       NULL ≠ ' ' (пробел - это символ).

Но всегда есть нюансы и исключения. Например, в Oracle INSERT INTO table (col) VALUES ('') запишет NULL. Это поведение отличается от других СУБД и часто становится сюрпризом при миграции.

Давайте рассмотрим разные ситуации, где саботаж NULL может проявиться.

1. Трёхзначная логика

Классическая булева логика (TRUE/FALSE) работает отлично, пока у вас есть данные. Но как только появляется NULL, логика становится трёхзначной: появляется состояние UNKNOWN.

·       TRUE AND UNKNOWN = UNKNOWN;

·       FALSE AND UNKNOWN = FALSE (это неинтуитивно, но так работает стандарт SQL — одного FALSE достаточно);

·       TRUE OR UNKNOWN = TRUE;

·       FALSE OR UNKNOWN = UNKNOWN;

·       NOT UNKNOWN = UNKNOWN. (отрицание неизвестности оставляет нас в неведении).

В  WHERE, CHECK условие считается истинным, только если выражение вернуло TRUE. Если вернулось FALSE или UNKNOWN — строка отклоняется / ограничение не нарушается (потому что для нарушения нужно FALSE, а не UNKNOWN).

Например:

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0)
);

В эту таблицу можно вставить NULL, потому что NULL > 0 → UNKNOWN, а UNKNOWN в CHECK-ограничении не является FALSE, значит, ограничение не нарушено.

Чтобы запретить NULL, нужно явно указать:

CREATE TABLE products (
    price DECIMAL(10,2) NOT NULL CHECK (price > 0)
);

2. Арифметика и агрегаты: NULL всё портит

Каждый разработчик рано или поздно натыкается на это:

SELECT 10 + NULL + 300; -- Результат: NULL
SELECT 57 * NULL;       -- Результат: NULL
SELECT NULL / 0;        -- Результат: NULL 

В большинстве случаев любая арифметическая операция с NULL превращает результат в NULL. Обратите внимание: NULL/0 возвращает NULL не потому, что деление на ноль разрешено, а потому что любой операнд NULL делает результат NULL. В отличие от этого, SELECT 10/0 в большинстве СУБД вызовет ошибку division by zero.

Но самое страшное происходит с агрегатными функциями. SUM, AVG и COUNT(column) тихо игнорируют NULL.

Например, AVG((1, NULL, 3)) даст  (1+3)/2 = 2, а не (1+0+3)/3 = 1.33.  Разница колоссальная. Если вы считаете среднюю зарплату в отделе, а у одного сотрудника она NULL, то этот сотрудник просто исчезнет из статистики, завысив средний показатель.

Важно запомнить разницу:

SELECT COUNT(*) FROM table;      -- Считает ВСЕ строки
SELECT COUNT(column) FROM table; -- Считает только строки, где column IS NOT NULL
SELECT COUNT(DISTINCT column) FROM table; -- Тоже игнорирует NULL в стандартном SQL

Всегда анализируйте пропуски! Если в данных много NULL, подумайте, действительно ли можно заменить их на 0 перед агрегацией, это может сильно менять смысл данных.

3. Великий парадокс: NULL не равен NULL

В математике любой объект равен самому себе. NULL — не объект. Это отсутствие объекта. Поэтому в стандартном SQL:

NULL = NULL -- Результат: UNKNOWN (в некоторых СУБД возвращает NULL)

Но в SQL Server решили «для простоты» ввести параметр ANSI_NULLS — он может быть установлен на уровне сессии, базы данных или даже конкретного запроса. Начиная с SQL Server 2005, по умолчанию он равен ON. В версиях 2016+ этот режим официально объявлен устаревшим и не рекомендуется к использованию. Результат — код, проработавший 10 лет в режиме OFF, может внезапно сломаться при смене настроек или миграции в облако.

То есть в зависимости от СУБД и настроек:

·       PostgreSQL, MySQL, Oracle: возвращают NULL.

·       SQL Server (параметр ANSI_NULLS ON): возвращает UNKNOWN.

·       SQL Server (параметр ANSI_NULLS OFF): возвращает TRUE. 

Почему нам так важно знать равны ли между собой NULL? Например, это поможет, когда в обычных JOIN начинают теряться строки.

-- Ошибка: NULL-строки потеряются
ON t1.attr = t2. NOT INattr
-- Безопасный вариант:
ON t1.attr = t2.attr OR (t1.attr IS NULL AND t2.attr IS NULL)

В PostgreSQL и свежих версиях SQL Server уже появился оператор IS [NOT] DISTINCT FROM. Он сравнивает два значения с учётом NULL, трактуя NULL = NULL как TRUE (в отличие от обычного =). Это реально упрощает жизнь при написании JOIN-условий и проверок на совпадение, избавляя от громоздких конструкций 

-- В PostgreSQL и свежих версиях SQL Server в качестве альтернативы можно использовать NOT DISTINCT:
ON   t1.attr IS NOT DISTINCT FROM t2.attr

4. Уникальность NULL — анархия

Как ведёт себя UNIQUE-индекс, если в нем есть NULL?

·       SQL Server: Разрешает только один NULL (жесткая диктатура).

·       PostgreSQL: Разрешает множество NULL (демократия с возможностью перейти к диктатуре).

Начиная с версии PostgreSQL 15, вы можете явно изменить это поведение при создании индекса с помощью опции NULLS NOT DISTINCT. В этом случае NULL будут считаться равными друг другу, и в индексе сможет находиться только одна такая запись, как в SQL Server

·       Oracle: Разрешает много NULL (демократия с техническим нюансом).

В стандартных B-деревьях индексов Oracle не хранит NULL-значения. Поскольку запись для NULL в индексе отсутствует, механизм проверки уникальности просто не срабатывает — отсюда возможность вставлять сколько угодно строк с NULL в уникальный столбец. Однако есть техническая уловка: если создать уникальный индекс с явным указанием порядка сортировки DESC, то NULL получает физическую запись в индексе, из-за чего уникальность начинает действовать.

Чем это все грозит проекту? Например, при миграции базы с PostgreSQL на SQL Server выяснилось, что 10 000 пользователей не указали email. На PostgreSQL это законно, а на SQL Server — нарушение уникальности. Привет, упавший прод!

5. «Смертельный номер»: NOT IN и подзапросы

Это одна из самых частых и фатальных ошибок. Никогда не используйте NOT IN с подзапросами, если есть риск получить NULL.

Почему это ломается? (спойлер, из-за трёхзначной логики)

-- Представьте, что у нас есть число A = 3, а подзапрос возвращает (1, 2, NULL)
A NOT IN (1, 2, NULL)
-- Эквивалентно:
A <> 1 AND A <> 2 AND A <> NULL
-- TRUE AND TRUE AND UNKNOWN = UNKNOWN

Результат - ни одной строки! Запрос вернет пустой результат, даже если данные есть. Решение: используйте NOT EXISTS:

SELECT * FROM A
WHERE NOT EXISTS (
    SELECT 1 FROM B WHERE B.id = A.id
);

Объясняется это тем, что:

NOT IN рассуждает: «этого значения нет среди всех элементов списка?» Один NULL делает ответ неопределенным. 

NOT EXISTS рассуждает: «нашлась хотя бы одна подходящая строка?» Если нет ни одной строки с условием TRUE, то NULL в остальных строках ничего не меняет.

6. DISTINCT, GROUP BY и операции над множествами.

Для UNION, EXCEPT, INTERSECT, DISTINCT, GROUP BY используется не логическое сравнение, а понятие «неразличимость» (indistinguishability). Эти операторы считают все NULL одинаковыми, поэтому один NULL в результате SELECT DISTINCT может скрывать за собой тысячи строк. Смиритесь и примите — это стандарт ANSI. 

И конечно, нюанс: в операторах UNION ALL, EXCEPT ALL и INTERSECT ALL поведение другое.

●      В UNION ALL NULL не сравниваются вообще — просто склеиваются все строки (будет столько NULL, сколько их в сумме).

●      В EXCEPT ALL считается количество вхождений. Если в левой таблице 5 NULL, а в правой 2 NULL, то результат даст 3 NULL (5 - 2). Здесь NULL тоже считаются «одинаковыми» для вычитания количества.

Гадкий NULL бывает полезен. Например, когда нужно сравнить два отчета, но в первом есть показатель, который отсутствует во втором отчете. Тогда если поставить NULL вместо отсутствующего показателя во второй отчет, то EXCEPT ALL даст полный достоверный результат. А если бы NULL не существовал, пришлось бы выбирать ноль, пробел, пустую строку или 'нет данных'. И каждый вариант исказил бы логику:

●      0 — сказал бы, что показатель есть, но равен нулю (ложь).

●      '' или пробел — сломали бы сравнение строк.

●      'нет данных' — вообще превратил бы число в строку.

А гадкий NULL берет эту ношу на себя: в EXCEPT ALL он считается «одинаковым» для подсчета кратности, но в сравнении всей строки (1000000 = NULL → UNKNOWN) он не дает вычесть строку там, где данные реально отличаются. Именно эта двойственность и спасает отчеты.

7.Оконные функции (LAG/LEAD)

Как понять, что вернул LAG? То, что предыдущей строки не существовало, или то, что в предыдущей строке было значение NULL?

·       В Oracle есть расширение IGNORE NULLS.

·       В PostgreSQL и SQL Server такого синтаксиса нет — нужно использовать явные проверки с ROW_NUMBER() или CASE.

Пример безопасного подхода:

SELECT 
    value,
    LAG(value) OVER w as prev,
    CASE WHEN LAG(value, 1, 'MAGIC_NULL') OVER w = 'MAGIC_NULL' THEN 'FIRST' END as boundary
  FROM data
  WINDOW w AS (ORDER BY id);

8. Сортировка (ORDER BY)

Где будут ваши NULL при сортировке? Это зависит от СУБД и настроек.

·       PostgreSQL/Oracle/MySQL (8.0 и выше): ASC → NULL в конце, DESC → NULL в начале.

·       SQL Server/MySQL (до 8.0): ASC → NULL в начале, DESC → NULL в конце.

Миграция с PostgreSQL на SQL Server перевернет ваш отчет с ног на голову. Спасает явное указание:

ORDER BY column ASC NULLS LAST;
ORDER BY column DESC NULLS FIRST;

Важно: NULLS FIRST/LAST поддерживается в PostgreSQL и Oracle, но не во всех версиях MySQL и вообще не поддерживается в SQL Server.

Выводы

В разных СУБД есть свои функции для работы с NULL:

·       в большинстве есть COALESCE(val1, val2, ...) и NULLIF(val1, val2),

·       в SQL Server ISNULL(val, default),

·       в MySQL IFNULL(val, default),

·       в Oracle NVL(val, default).

Нюанс: в SQL Server ISNULL и COALESCE ведут себя по-разному с типами данных. ISNULL использует тип первого аргумента, а COALESCE — тип с наивысшим приоритетом. Это может привести к неожиданным ошибкам приведения типов.

И, конечно, каждая СУБД имеет свои причуды.

Чек-лист выживания

1.     Для сравнений: Всегда используйте IS NULL/IS NOT NULL.

2.     Для подзапросов: NOT IN — табу. Используйте NOT EXISTS.

3.     Для агрегатов: Помните, что они игнорируют NULL. Применяйте COALESCE до агрегации, если нужны нули.

4.     Для сортировки: Всегда указывайте NULLS FIRST/NULLS LAST где это возможно.

5.     Для данных: Навешивайте NOT NULL на схему там, где данные обязательны.

6.     Знайте свою СУБД: Поведение NULL отличается. Тестируйте краевые случаи на целевой платформе.

7.     Для оконных функций: Не доверяйте LAG/LEAD с NULL — используйте явные проверки.

8.     Согласуйте подход к NULL: Прежде чем заменять NULL на 0, исключать такие строки или оставлять как есть — изучите описание таблицы или обратитесь к специалистам, которые её поддерживают. Универсальной стратегии нет — есть только согласованная.

NULL никуда не денется в обозримом будущем, поэтому стратегия выживания проста: в каждом запросе, всегда задавая вопрос «а что здесь с NULL?», и помните, что NOT NULL в схеме — это хорошо, но не панацея; NULL может прилететь из подзапроса, джойна или агрегатной функции, поэтому проверки должны быть на всех уровнях.

Взгляд в будущее

В экспериментальных СУБД и современных языках программирования (Rust, Swift, Kotlin) уже появились опциональные типы (Optional<T>, Option<T>), которые заставляют разработчика явно обрабатывать отсутствие значения на этапе компиляции.

В SQL-мире тоже есть движения:

·       Некоторые исследовательские СУБД вводят строгие режимы, где WHERE col = NULL вызывает ошибку компиляции.

·       В PostgreSQL есть расширения для статической проверки запросов.

·       В PostgreSQL (и некоторых других СУБД) вы можете создать доменный тип — это, по сути, пользовательский тип с ограничениями. Он позволяют поднять логику работы с NULL на уровень DDL. Но у них есть свои минусы.

Пока мы живем в мире SQL, и на всякое исключение есть свой нюанс: будьте бдительны, и пусть NULL не крадёт ваши данные!

P.S. Наверняка, нюансов больше, чем в статье и у вас есть свои истории про NULL — делитесь в комментариях. Чем больше мы знаем о его повадках, тем меньше шансов, что он нас обманет.