NULL и троичная логика: ловушки

Почему запрос «правильный», а строки молча пропадают — и при чём тут третье логическое значение.

NULL в SQL — это не «ноль» и не «пустая строка», а маркер «значение неизвестно». Любое сравнение с неизвестным даёт не TRUE и не FALSE, а третий результат — UNKNOWN.

Большинство багов с NULL рождаются из одной иллюзии: что логика в SQL двоичная. На самом деле она троичная: TRUE, FALSE и UNKNOWN. Условие WHERE пропускает строку, только если результат строго TRUE — а UNKNOWN отбрасывает молча, без ошибки. Отсюда «исчезающие» строки, которые тяжело отлаживать.

NULL ≠ NULL

Самое контринтуитивное: NULL не равен даже самому себе. Сравнение NULL = NULL возвращает не TRUE, а UNKNOWN — ведь «неизвестно равно неизвестному?» честно неизвестно. Поэтому проверять на NULL через = бесполезно; для этого есть отдельные операторы IS NULL и IS NOT NULL.

CREATE TABLE t (id INTEGER PRIMARY KEY, a INTEGER, b INTEGER);
INSERT INTO t (a, b) VALUES (1, 1), (NULL, NULL), (2, NULL);

-- Неверно: '=' с NULL не ловит NULL-строки
SELECT id FROM t WHERE a = b;

-- Верно: явная проверка через IS NULL
SELECT id FROM t WHERE a IS NULL;

Вывод:

1
2

Первый запрос вернул только строку 1 (где 1 = 1): для строки 2 сравнение NULL = NULL дало UNKNOWN, для строки 3 — 2 = NULL тоже UNKNOWN, обе отброшены. Второй запрос через IS NULL корректно нашёл строку 2.

Троичная логика в AND/OR

UNKNOWN распространяется по логическим операторам по своим правилам. Запомнить помогает «здравый смысл неопределённости»: TRUE OR UNKNOWN = TRUE (одной правды достаточно для OR), FALSE AND UNKNOWN = FALSE (одной лжи достаточно для AND), а TRUE AND UNKNOWN = UNKNOWN и NOT UNKNOWN = UNKNOWN.

Главная ловушка: NOT IN с NULL

Это баг, на котором спотыкаются даже опытные. Если в списке для NOT IN есть хотя бы один NULL, запрос внезапно возвращает пустой результат — и формально он прав. Почему: x NOT IN (1, NULL) разворачивается в x <> 1 AND x <> NULL. Второе сравнение всегда UNKNOWN, а TRUE AND UNKNOWN = UNKNOWN — строка не проходит. Воспроизведём.

CREATE TABLE customers (id INTEGER PRIMARY KEY, city TEXT);
INSERT INTO customers (city) VALUES ('Москва'), ('Казань'), ('Сочи');

CREATE TABLE blocked (city TEXT);
INSERT INTO blocked (city) VALUES ('Сочи'), (NULL);

-- Ловушка: из-за NULL в blocked результат ПУСТОЙ
SELECT city FROM customers
WHERE city NOT IN (SELECT city FROM blocked);

Вывод:

Ожидали Москву и Казань — получили ничего. Надёжное решение — NOT EXISTS, которому NULL не страшен, потому что он проверяет факт наличия строки, а не сравнивает значения:

CREATE TABLE customers (id INTEGER PRIMARY KEY, city TEXT);
INSERT INTO customers (city) VALUES ('Москва'), ('Казань'), ('Сочи');
CREATE TABLE blocked (city TEXT);
INSERT INTO blocked (city) VALUES ('Сочи'), (NULL);

SELECT c.city FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM blocked b WHERE b.city = c.city
);

Вывод:

Москва
Казань

COALESCE и NULLIF

Две функции-выручалки. COALESCE(a, b, c) возвращает первый не-NULL аргумент — удобно подставлять значение по умолчанию. NULLIF(a, b) возвращает NULL, если a = b, иначе a — классически используется, чтобы превратить «пустую строку» или ноль в NULL и избежать деления на ноль.

SELECT
  COALESCE(NULL, NULL, 'по умолчанию') AS first_nonnull,
  COALESCE(NULL, 42)                    AS num,
  NULLIF(10, 10)                        AS same,
  NULLIF(10, 5)                         AS diff;

Вывод:

по умолчанию|42||10

Третья колонка пустая — это NULL (10 = 10), четвёртая равна 10 (10 ≠ 5). Связка COALESCE(x / NULLIF(y, 0), 0) — стандартный приём «деление без падения на нуле».

EXISTS vs IN vs NOT IN

• IN — нормально работает для положительной проверки, NULL в подзапросе ему обычно не вредит (он ищет совпадение, а не его отсутствие).
• NOT IN — опасен при возможных NULL в списке: молча даёт пустой результат. Используйте только если уверены, что NULL там нет (например, колонка NOT NULL).
• EXISTS / NOT EXISTS — устойчивы к NULL и часто эффективнее: проверяют наличие строки, а не равенство значений. Это безопасный выбор по умолчанию.

Как это работает под капотом

NULL — это не значение, а флаг «нет данных», хранящийся отдельно (битовая маска NULL у строки). Любой арифметический или сравнительный оператор, встретив этот флаг, по стандарту возвращает NULL/UNKNOWN, не пытаясь «угадать». WHERE, ON и HAVING пропускают строку только при TRUE, поэтому UNKNOWN ведёт себя как «нет». А вот GROUP BY и DISTINCT особые: они считают все NULL одной группой (для них «неизвестные» неразличимы между собой), хотя в сравнении те же NULL не равны. Эта двойственность и сбивает с толку.

Частые ошибки

• Проверяют NULL через = NULL или <> NULL. Всегда UNKNOWN. Нужны IS NULL / IS NOT NULL.
• Используют NOT IN с подзапросом, где бывает NULL. Тихо пустой результат. Замена — NOT EXISTS.
• Считают, что WHERE x <> 'A' вернёт и строки с NULL в x. Нет: NULL <> 'A' = UNKNOWN, строка отброшена. Добавьте OR x IS NULL.
• Складывают NULL в арифметике. 5 + NULL = NULL. Оборачивайте слагаемые в COALESCE(col, 0).

Итоги

• NULL — «неизвестно», а не ноль; NULL = NULL даёт UNKNOWN.
• Логика троичная: WHERE пропускает только строго TRUE, UNKNOWN молча отбрасывает.
• Проверяйте NULL через IS NULL / IS NOT NULL, не через =.
• NOT IN с NULL в списке → пустой результат; берите NOT EXISTS.
• COALESCE подставляет дефолт, NULLIF превращает значение в NULL (и спасает от деления на ноль).