NULL and IS DISTINCT FROM: NULL-Safe Equality in SQL

NULL — одна из самых важных и одновременно самых коварных тем в SQL.

На первый взгляд кажется, что NULL — это просто пустое значение. Например, пользователь не указал страну, у сотрудника нет руководителя, у заказа ещё нет даты оплаты.

Но в SQL NULL означает не «пусто», не 0, не пустую строку '', а:

значение неизвестно или отсутствует.

И из-за этого привычные сравнения начинают вести себя не так, как ожидает новичок.

Например, такой запрос кажется логичным:

SELECT id, email
FROM users
WHERE country = NULL;

Хочется думать, что он найдёт пользователей, у которых страна не указана.

Но на самом деле он не найдёт никого.

Почему? Потому что с NULL нельзя сравнивать через обычное =.

Для проверки на отсутствие значения в SQL используются специальные условия:

IS NULL
IS NOT NULL

А если нужно безопасно сравнивать два значения, среди которых может быть NULL, в PostgreSQL есть очень полезные операторы:

IS DISTINCT FROM
IS NOT DISTINCT FROM

Разберём всё спокойно и по шагам.

Что такое NULL простыми словами

Представим таблицу users:

id | email           | country
---+-----------------+---------
1  | anna@mail.com   | Vietnam
2  | bob@mail.com    | NULL
3  | kate@mail.com   | Germany
4  | tom@mail.com    | NULL

У пользователей Bob и Tom страна не указана.

Это не значит, что страна равна пустой строке:

''

И не значит, что страна равна слову:

'NULL'

NULL — это специальное состояние в базе данных.

Оно означает:

мы не знаем значение или значение отсутствует или оно пока не было заполнено

Именно поэтому SQL относится к NULL осторожно.

Если значение неизвестно, база не может честно сказать, равно оно чему-то или нет.

Почему country = NULL не работает

Допустим, мы хотим найти пользователей без страны.

Новичок может написать так:

SELECT id, email
FROM users
WHERE country = NULL;

Но это неправильный запрос.

Он не вернёт строки, даже если в колонке country действительно есть NULL.

Почему?

Потому что любое обычное сравнение с NULL даёт не TRUE и не FALSE, а третье состояние — UNKNOWN.

Например:

SELECT NULL = NULL;

Результат будет не true.

В SQL это выражение считается неизвестным.

То же самое:

SELECT NULL = 1;
SELECT NULL <> 1;
SELECT NULL = 'Vietnam';

Все эти сравнения дают UNKNOWN, потому что база не знает, что лежит на месте NULL.

Можно объяснить это по-человечески.

Если мы говорим:

неизвестное значение = неизвестное значение

мы не можем честно сказать «да». Ведь это могут быть два одинаковых значения, а могут быть два разных.

Если мы говорим:

неизвестное значение = Vietnam

мы тоже не можем сказать «да» или «нет». Может быть там Vietnam, а может быть что-то другое.

Поэтому SQL отвечает: неизвестно.

Трёхзначная логика в SQL

В обычной логике мы привыкли к двум вариантам:

TRUE
FALSE

В SQL из-за NULL есть три варианта:

TRUE
FALSE
UNKNOWN

Это называется трёхзначная логика.

Для обычного WHERE важно следующее правило:

WHERE пропускает только строки, где условие равно TRUE.

Если условие равно FALSE, строка не проходит.

Если условие равно UNKNOWN, строка тоже не проходит.

Именно поэтому такой запрос ничего не находит:

SELECT id, email
FROM users
WHERE country = NULL;

Для строк, где country равен NULL, условие country = NULL превращается в UNKNOWN.

А WHERE не пропускает UNKNOWN.

Как правильно проверять NULL

Для проверки на NULL используются специальные условия:

IS NULL
IS NOT NULL

Чтобы найти пользователей, у которых страна не указана, нужно писать так:

SELECT id, email
FROM users
WHERE country IS NULL;

Результат:

id | email
---+---------------
2  | bob@mail.com
4  | tom@mail.com

А чтобы найти пользователей, у которых страна указана:

SELECT id, email, country
FROM users
WHERE country IS NOT NULL;

Результат:

id | email          | country
---+----------------+---------
1  | anna@mail.com  | Vietnam
3  | kate@mail.com  | Germany

Можно запомнить простое правило:

= NULL почти всегда ошибка. Для проверки отсутствия значения используйте IS NULL.

И наоборот:

<> NULL тоже ошибка. Для проверки, что значение есть, используйте IS NOT NULL.

NULL — это не пустая строка и не ноль

Это важный момент.

NULL, пустая строка и ноль — разные вещи.

NULL  -- значения нет или оно неизвестно
''    -- значение есть, это пустая строка
0     -- значение есть, это число ноль

Например:

id | name
---+------
1  | Anna
2  |
3  | NULL

Во второй строке имя может быть пустой строкой ''.

В третьей строке имя отсутствует, то есть NULL.

Это разные состояния.

Проверка на пустую строку:

SELECT id
FROM users
WHERE name = '';

Проверка на отсутствие значения:

SELECT id
FROM users
WHERE name IS NULL;

Если нужно найти оба варианта — и пустую строку, и NULL, можно написать так:

SELECT id
FROM users
WHERE name IS NULL
   OR name = '';

В реальных данных это встречается часто: одна система прислала NULL, другая — пустую строку, а третья — строку с пробелом.

Поэтому при очистке данных иногда используют ещё и TRIM:

SELECT id
FROM users
WHERE name IS NULL
   OR TRIM(name) = '';

Такой запрос найдёт:

• NULL;
• пустую строку;
• строку из одних пробелов.

Арифметика с NULL

NULL влияет не только на сравнения, но и на вычисления.

Например:

SELECT 100 + NULL;

Результат будет:

NULL

Почему?

Потому что если одно из значений неизвестно, итог тоже неизвестен.

То же самое:

SELECT 100 - NULL;
SELECT 100 * NULL;
SELECT 100 / NULL;

Результатом будет NULL.

Представим заказ:

amount = 1000
discount = NULL

Если написать:

SELECT amount - discount AS final_amount
FROM orders;

то для строки, где discount равен NULL, результат тоже будет NULL.

Хотя в бизнес-смысле мы, возможно, хотели считать отсутствующую скидку как 0.

Для таких случаев используют COALESCE.

SELECT
  amount - COALESCE(discount, 0) AS final_amount
FROM orders;

COALESCE(discount, 0) означает:

если discount не NULL, возьми его; если discount равен NULL, используй 0.

Это отдельная важная тема, но здесь главное понять принцип:

NULL распространяется по выражению. Если часть вычисления неизвестна, результат часто тоже становится неизвестным.

Почему NULL = NULL не TRUE

Самое непривычное для новичков:

SELECT NULL = NULL;

Это не TRUE.

Почему?

Потому что NULL — это не конкретное значение. Это «неизвестно».

Если у двух людей в анкете не указана страна, это не значит, что у них одинаковая страна. Может быть, у одного Vietnam, у другого Germany, просто мы этого не знаем.

Поэтому SQL не говорит:

NULL = NULL → TRUE

Он говорит:

NULL = NULL → UNKNOWN

Из-за этого обычное сравнение двух колонок тоже может вести себя неожиданно.

Проблема сравнения двух колонок с NULL

Допустим, у нас есть две таблицы.

Основная таблица users:

id | email          | country
---+----------------+---------
1  | anna@mail.com  | Vietnam
2  | bob@mail.com   | NULL
3  | kate@mail.com  | Germany

И новая таблица из импорта staging_users:

id | email          | country
---+----------------+---------
1  | anna@mail.com  | Vietnam
2  | bob@mail.com   | NULL
3  | kate@mail.com  | France

Мы хотим найти пользователей, у которых изменилась страна.

На первый взгляд можно написать:

SELECT u.id
FROM users u
JOIN staging_users s ON s.id = u.id
WHERE u.country <> s.country;

Проблема в том, что если в обеих таблицах country равен NULL, сравнение:

NULL <> NULL

не даст FALSE.

Оно даст UNKNOWN.

Для WHERE это означает: строка не попадёт в результат.

Иногда это вроде бы совпадает с ожиданием. Но проблема появляется, когда одно значение NULL, а другое нет.

Например:

старое значение: NULL
новое значение: Vietnam

Сравнение:

NULL <> 'Vietnam'

тоже даёт UNKNOWN, а не TRUE.

То есть обычный <> может не заметить реальное изменение.

Именно здесь нужен IS DISTINCT FROM.

IS DISTINCT FROM: безопасное сравнение с NULL

Оператор IS DISTINCT FROM сравнивает значения так, как обычно ожидает человек.

Он считает NULL отдельным сравнимым состоянием.

Примеры:

SELECT NULL IS DISTINCT FROM NULL;

Результат:

false

Потому что оба значения отсутствуют, значит они не различаются.

SELECT NULL IS DISTINCT FROM 1;

Результат:

true

Потому что одно значение отсутствует, а второе равно 1.

SELECT 1 IS DISTINCT FROM 1;

Результат:

false

Потому что значения одинаковые.

SELECT 1 IS DISTINCT FROM 2;

Результат:

true

Потому что значения разные.

Можно думать так:

IS DISTINCT FROM отвечает на вопрос: «Эти значения реально отличаются друг от друга?»

И в отличие от <>, он нормально работает с NULL.

IS NOT DISTINCT FROM: безопасное равенство с NULL

Есть и обратный оператор:

IS NOT DISTINCT FROM

Он означает:

значения не отличаются друг от друга

То есть это похоже на =, но безопасно для NULL.

Примеры:

SELECT NULL IS NOT DISTINCT FROM NULL;

Результат:

true

SELECT 1 IS NOT DISTINCT FROM 1;

Результат:

true

SELECT NULL IS NOT DISTINCT FROM 1;

Результат:

false

SELECT 1 IS NOT DISTINCT FROM 2;

Результат:

false

Можно запомнить так:

a IS DISTINCT FROM b      -- a и b отличаются
a IS NOT DISTINCT FROM b  -- a и b одинаковые, включая случай NULL и NULL

Таблица поведения

Сравним обычное = и IS NOT DISTINCT FROM.

a       | b       | a = b     | a IS NOT DISTINCT FROM b
--------+---------+-----------+---------------------------
1       | 1       | TRUE      | TRUE
1       | 2       | FALSE     | FALSE
NULL    | 1       | UNKNOWN   | FALSE
1       | NULL    | UNKNOWN   | FALSE
NULL    | NULL    | UNKNOWN   | TRUE

Главная разница в последней строке.

Для обычного =:

NULL = NULL

это UNKNOWN.

Для IS NOT DISTINCT FROM:

NULL IS NOT DISTINCT FROM NULL

это TRUE.

Именно поэтому этот оператор так полезен при сравнении колонок, где NULL считается допустимым значением.

Практический пример: найти реально изменившиеся строки

Один из самых полезных сценариев — поиск изменений при загрузке данных.

Допустим, у нас есть основная таблица users и временная таблица staging_users, куда загрузили новые данные из внешнего источника.

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

Плохой вариант:

SELECT u.id
FROM users u
JOIN staging_users s ON s.id = u.id
WHERE u.name <> s.name
   OR u.country <> s.country
   OR u.email <> s.email;

Почему плохой?

Потому что если одно из значений стало из NULL обычным значением или наоборот, обычный <> может не сработать так, как мы хотим.

Например:

u.country = NULL
s.country = 'Vietnam'

Сравнение:

u.country <> s.country

даст UNKNOWN, а не TRUE.

Правильный вариант:

SELECT u.id
FROM users u
JOIN staging_users s ON s.id = u.id
WHERE u.name    IS DISTINCT FROM s.name
   OR u.country IS DISTINCT FROM s.country
   OR u.email   IS DISTINCT FROM s.email;

Теперь SQL честно найдёт строки, где значение реально изменилось:

• было NULL, стало 'Vietnam';
• было 'Vietnam', стало NULL;
• было 'Germany', стало 'France';
• было NULL, осталось NULL — не считается изменением.

Это очень удобно для ETL, импорта данных, синхронизации справочников и обновления витрин.

Пример: обновить только изменившиеся строки

На практике такой подход часто используют перед UPDATE.

Например:

UPDATE users u
SET
  name = s.name,
  country = s.country,
  email = s.email
FROM staging_users s
WHERE s.id = u.id
  AND (
       u.name    IS DISTINCT FROM s.name
    OR u.country IS DISTINCT FROM s.country
    OR u.email   IS DISTINCT FROM s.email
  );

Такой запрос обновит только те строки, где действительно есть изменения.

Почему это хорошо?

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

• производительности;
• аудита;
• триггеров;
• поля updated_at;
• репликации;
• логов изменений.

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

Пример: дедупликация с NULL

Ещё один полезный сценарий — поиск дублей.

Допустим, мы считаем пользователя дублем, если у него совпадают email и country.

Но country может быть NULL.

Таблица:

id | email          | country
---+----------------+---------
1  | anna@mail.com  | Vietnam
2  | anna@mail.com  | Vietnam
3  | bob@mail.com   | NULL
4  | bob@mail.com   | NULL

Если написать обычный JOIN:

SELECT a.id, b.id
FROM users a
JOIN users b
  ON a.email = b.email
 AND a.country = b.country
WHERE a.id < b.id;

Пара с bob@mail.com может не найтись, потому что:

NULL = NULL

не даёт TRUE.

Чтобы считать две строки дублями, даже если country равен NULL в обеих, используем IS NOT DISTINCT FROM.

SELECT a.id, b.id
FROM users a
JOIN users b
  ON a.email IS NOT DISTINCT FROM b.email
 AND a.country IS NOT DISTINCT FROM b.country
WHERE a.id < b.id;

Теперь строки:

3 | bob@mail.com | NULL
4 | bob@mail.com | NULL

будут считаться дублями.

Это как раз тот случай, где обычное = не подходит, а IS NOT DISTINCT FROM выражает нашу мысль точно.

Частая ловушка: NOT IN и NULL

NULL может ломать не только обычные сравнения, но и NOT IN.

Допустим, у нас есть таблица employees:

id | name   | manager_id
---+--------+-----------
1  | Anna   | NULL
2  | Bob    | 1
3  | Kate   | 1
4  | Tom    | 2

Мы хотим найти сотрудников, которые не являются менеджерами.

Можно подумать о таком запросе:

SELECT id, name
FROM employees
WHERE id NOT IN (
  SELECT manager_id
  FROM employees
);

Но здесь есть ловушка.

Подзапрос:

SELECT manager_id
FROM employees

вернёт примерно такой список:

NULL
1
1
2

И из-за NULL внутри списка NOT IN может начать вести себя неожиданно: результат может оказаться пустым.

Почему?

Потому что условие:

id NOT IN (NULL, 1, 2)

для SQL превращается в сравнения, где присутствует неизвестное значение.

А если среди вариантов есть NULL, база не может уверенно сказать, что id точно не равен неизвестному значению.

Как безопасно писать вместо NOT IN

Есть два хороших варианта.

Первый — отфильтровать NULL в подзапросе:

SELECT id, name
FROM employees
WHERE id NOT IN (
  SELECT manager_id
  FROM employees
  WHERE manager_id IS NOT NULL
);

Теперь в списке не будет NULL, и NOT IN станет безопаснее.

Второй вариант — использовать NOT EXISTS.

SELECT e.id, e.name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
);

Этот запрос читается так:

найди сотрудников, для которых не существует другого сотрудника, у которого manager_id равен их id.

NOT EXISTS часто считается более надёжным вариантом, особенно когда в данных могут быть NULL.

Для новичка полезно запомнить:

NOT IN и NULL — опасное сочетание. Если в подзапросе может быть NULL, лучше использовать NOT EXISTS или явно убрать NULL.

NULL в агрегатных функциях

Ещё один важный момент: многие агрегатные функции игнорируют NULL.

Например, есть таблица заказов:

id | amount
---+--------
1  | 100
2  | 200
3  | NULL

Запрос:

SELECT COUNT(*) AS rows_count
FROM orders;

вернёт:

3

Потому что COUNT(*) считает строки.

А вот:

SELECT COUNT(amount) AS amount_count
FROM orders;

вернёт:

2

Потому что COUNT(amount) считает только строки, где amount не NULL.

То же самое с SUM, AVG, MIN, MAX: они обычно игнорируют NULL.

SELECT
  SUM(amount) AS total_amount,
  AVG(amount) AS avg_amount
FROM orders;

В расчёт попадут только значения 100 и 200.

Это часто удобно, но важно понимать, что происходит. Если NULL в вашем бизнес-смысле должен считаться как 0, нужно явно использовать COALESCE.

SELECT
  AVG(COALESCE(amount, 0)) AS avg_amount_with_zero
FROM orders;

Но делать так нужно осознанно. NULL и 0 — разные вещи.

Когда использовать IS NULL, а когда IS DISTINCT FROM

Здесь легко запутаться, поэтому разделим сценарии.

Если нужно проверить, есть значение или нет, используем:

IS NULL
IS NOT NULL

Примеры:

SELECT id, email
FROM users
WHERE country IS NULL;

SELECT id, email
FROM users
WHERE country IS NOT NULL;

Если нужно сравнить два значения, и среди них может быть NULL, используем:

IS DISTINCT FROM
IS NOT DISTINCT FROM

Примеры:

SELECT u.id
FROM users u
JOIN staging_users s ON s.id = u.id
WHERE u.country IS DISTINCT FROM s.country;

SELECT a.id, b.id
FROM users a
JOIN users b
  ON a.email IS NOT DISTINCT FROM b.email
 AND a.country IS NOT DISTINCT FROM b.country;

Коротко:

IS NULL                 -- значение отсутствует
IS NOT NULL             -- значение есть
IS DISTINCT FROM        -- значения отличаются, NULL учитывается безопасно
IS NOT DISTINCT FROM    -- значения одинаковые, NULL = NULL считается совпадением

Аналог в MySQL

В MySQL тоже есть NULL, и обычное сравнение с ним через = работает не так, как ожидают новички.

Для проверки на отсутствие значения используется тот же синтаксис:

WHERE country IS NULL

И:

WHERE country IS NOT NULL

Но вместо PostgreSQL-оператора:

IS NOT DISTINCT FROM

в MySQL часто используют оператор:

<=>

Это NULL-безопасное равенство.

Примеры:

SELECT NULL <=> NULL;

Результат:

1

SELECT NULL <=> 1;

Результат:

0

SELECT 1 <=> 1;

Результат:

1

То есть в MySQL:

a <=> b

примерно соответствует PostgreSQL:

a IS NOT DISTINCT FROM b

А чтобы проверить, что значения отличаются, можно использовать отрицание:

NOT (a <=> b)

Что насчёт ClickHouse

В ClickHouse тоже есть типы с поддержкой NULL, которые обычно называют Nullable.

Для проверки на NULL используются функции:

isNull(value)
isNotNull(value)

Например:

SELECT *
FROM users
WHERE isNull(country);

Или:

SELECT *
FROM users
WHERE isNotNull(country);

Для NULL-безопасного сравнения в ClickHouse часто используют явную обработку через функции вроде coalesce или ifNull, но конкретный лучший вариант зависит от версии ClickHouse, настроек и типов данных.

Например, если в вашей задаче можно заменить NULL на специальное значение, которого точно нет в данных, можно сделать так:

coalesce(country, '__NULL__') = coalesce(other_country, '__NULL__')

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

Главная идея остаётся такой же:

если в сравнении участвует NULL, не полагайтесь на обычное =, пока не понимаете, как именно ваша СУБД обрабатывает такие случаи.

Практические шаблоны

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

SELECT id, email
FROM users
WHERE country IS NULL;

Найти строки, где значение заполнено

SELECT id, email
FROM users
WHERE country IS NOT NULL;

Заменить NULL на значение по умолчанию

SELECT
  id,
  COALESCE(country, 'Unknown') AS country
FROM users;

Найти строки, где значение изменилось

SELECT u.id
FROM users u
JOIN staging_users s ON s.id = u.id
WHERE u.country IS DISTINCT FROM s.country;

Сравнить две колонки как равные, включая NULL и NULL

SELECT a.id, b.id
FROM users a
JOIN users b
  ON a.email IS NOT DISTINCT FROM b.email
 AND a.country IS NOT DISTINCT FROM b.country;

Безопаснее заменить NOT IN на NOT EXISTS

SELECT e.id, e.name
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM employees m
  WHERE m.manager_id = e.id
);

Если используете NOT IN, уберите NULL из подзапроса

SELECT id, name
FROM employees
WHERE id NOT IN (
  SELECT manager_id
  FROM employees
  WHERE manager_id IS NOT NULL
);

Что важно запомнить

NULL в SQL означает не ноль и не пустую строку, а неизвестное или отсутствующее значение.

Поэтому обычные сравнения с NULL не работают так, как многие ожидают.

Неправильно:

WHERE country = NULL

Правильно:

WHERE country IS NULL

Неправильно:

WHERE country <> NULL

Правильно:

WHERE country IS NOT NULL

Если нужно сравнить две колонки, где может быть NULL, обычные = и <> могут дать неожиданный результат.

Для безопасного сравнения в PostgreSQL используйте:

IS DISTINCT FROM

и:

IS NOT DISTINCT FROM

Пример поиска изменений:

WHERE old_value IS DISTINCT FROM new_value

Пример безопасного равенства:

WHERE old_value IS NOT DISTINCT FROM new_value

Короткий вывод

NULL — это особое состояние в SQL. Оно означает, что значение отсутствует или неизвестно.

Из-за этого SQL использует трёхзначную логику:

TRUE
FALSE
UNKNOWN

Именно поэтому сравнения вроде:

NULL = NULL

не дают TRUE.

Для проверки отсутствия значения используйте:

IS NULL
IS NOT NULL

А для сравнения значений, где NULL должен учитываться безопасно, используйте:

IS DISTINCT FROM
IS NOT DISTINCT FROM

Главная мысль простая:

IS NULL отвечает на вопрос «значение отсутствует?» IS DISTINCT FROM отвечает на вопрос «значения реально отличаются?»

Если это понять, большая часть странного поведения NULL в SQL перестаёт быть магией и становится нормальной, предсказуемой логикой.