NULL и трёхзначная логика: почему NULL ломает интуицию

В исходной реляционной модели Codd 1970 года NULL не было. Codd добавил его позже, отдельной работой, как способ выразить «значение неизвестно или неприменимо». NULL — самое спорное понятие в SQL: оно решает реальную проблему, но взамен заставляет логику запросов работать не по правилам школьной алгебры. Junior-инженер, который не понимает NULL, обязательно напишет запрос, который «теряет строки» или «считает не то» — и не поймёт, почему.

Этот урок объясняет, что такое NULL, как из-за него обычная двузначная логика превращается в трёхзначную, и какие конкретные ловушки из этого следуют.

Что такое NULL и чем он не является

NULL — это не значение. Это маркер отсутствия значения. Когда атрибут tuple равен NULL, это означает «здесь значения нет» — по одной из двух причин:

• Неизвестно (unknown): значение существует, но мы его не знаем. У клиента есть телефон, но он его не указал.
• Неприменимо (not applicable): значения не существует в принципе. У товара «цифровая книга» нет атрибута «вес посылки».

SQL не различает эти два случая — оба обозначаются одним NULL, и это само по себе источник неоднозначности.

Критично понять, чем NULL не является:

• NULL — это не ноль. Ноль 0 — это известное число. NULL — отсутствие числа.
• NULL — это не пустая строка ''. Пустая строка — это известная строка длины 0. NULL — отсутствие строки.
• NULL — это не FALSE. FALSE — известное логическое значение.

SELECT
    0 = 0          AS zero_eq_zero,      -- TRUE: ноль равен нулю
    '' = ''        AS empty_eq_empty,    -- TRUE: пустая строка равна себе
    NULL = NULL    AS null_eq_null;      -- НЕ TRUE! результат UNKNOWN
-- zero_eq_zero | empty_eq_empty | null_eq_null
-- -------------+----------------+--------------
-- t            | t              | (NULL)

Последний столбец — главная странность урока. NULL = NULL не равно TRUE. Разберём, почему.

Откуда берётся третье значение UNKNOWN

В обычной (булевой) логике у выражения два исхода: TRUE или FALSE. На этом построена вся школьная алгебра логики.

NULL это ломает. Рассмотрим предикат age > 25 для строки, где age равен NULL. Значение возраста неизвестно. Больше ли неизвестное число, чем 25? Ответ — неизвестно. Не TRUE и не FALSE, а третий, честный исход: UNKNOWN.

Поэтому SQL использует трёхзначную логику (three-valued logic, 3VL): любой предикат может дать TRUE, FALSE или UNKNOWN. И главное правило:

Любое сравнение, в котором участвует NULL, даёт UNKNOWN.

NULL = 5      -> UNKNOWN   (равно ли неизвестное пяти? неизвестно)
NULL <> 5     -> UNKNOWN   (не равно ли? тоже неизвестно)
NULL > 5      -> UNKNOWN
NULL = NULL   -> UNKNOWN   (равны ли два неизвестных? неизвестно)
NULL + 5      -> NULL      (арифметика с NULL даёт NULL)

NULL = NULL даёт UNKNOWN, потому что мы сравниваем два неизвестных значения: вдруг они на самом деле разные? Логика не может утверждать TRUE. Это не каприз стандарта — это честность: SQL отказывается врать, что два неизвестных значения равны.

Таблицы истинности с UNKNOWN

Логические операторы AND, OR, NOT тоже расширены на третье значение. Запоминать таблицы наизусть не нужно — достаточно понять принцип: UNKNOWN значит «может быть TRUE, может быть FALSE».

AND:  результат TRUE только если ОБА аргумента TRUE.
      TRUE  AND UNKNOWN = UNKNOWN  (исход зависит от неизвестного)
      FALSE AND UNKNOWN = FALSE    (FALSE и так делает всё FALSE)

OR:   результат TRUE если ХОТЯ БЫ один аргумент TRUE.
      TRUE  OR  UNKNOWN = TRUE     (TRUE и так делает всё TRUE)
      FALSE OR  UNKNOWN = UNKNOWN  (исход зависит от неизвестного)

NOT:  NOT UNKNOWN = UNKNOWN        (не знаем X -> не знаем и not X)

Логика здесь интуитивна, если думать так: «если подставить вместо UNKNOWN и TRUE, и FALSE — изменится ли результат? Если нет — результат определён; если да — UNKNOWN».

Ловушка 1: WHERE пропускает только TRUE

Вот первое практическое следствие, на котором спотыкаются все. Клауза WHERE оставляет строку, только если предикат дал TRUE. Строки, для которых предикат дал FALSE ИЛИ UNKNOWN, отбрасываются одинаково.

Рассмотрим таблицу users с тремя строками — у Cy возраст не указан (NULL):

-- users:
-- name | age
-- -----+------
-- Ann  | 30
-- Bob  | 20
-- Cy   | NULL

SELECT name FROM users WHERE age > 25;
-- name
-- ----
-- Ann            <- только Ann; для Cy предикат UNKNOWN, строка отброшена

SELECT name FROM users WHERE age <= 25;
-- name
-- ----
-- Bob            <- только Bob; для Cy снова UNKNOWN

-- Cy НЕ попал НИ в один из двух запросов!

Интуитивно age > 25 и age <= 25 кажутся «противоположностями», которые вместе покрывают всех. Но строка с age = NULL не попадает ни в один из них, потому что для неё оба предиката дают UNKNOWN, а WHERE пропускает только TRUE. Это и есть «потерянные строки» — самая частая NULL-ошибка. Если в таблице есть NULL, и вы хотите учесть такие строки, их нужно обрабатывать явно: WHERE age > 25 OR age IS NULL.

Ловушка 2: проверять NULL надо через IS NULL

Раз age = NULL всегда даёт UNKNOWN, проверить «здесь NULL?» через знак равенства невозможно. Для этого есть специальный оператор IS NULL (и IS NOT NULL), который возвращает честные TRUE/FALSE.

-- НЕ работает: = NULL всегда UNKNOWN, строк не будет
SELECT name FROM users WHERE age = NULL;
-- (пусто — ни одной строки, хотя Cy есть)

-- Работает: IS NULL — специальный оператор
SELECT name FROM users WHERE age IS NULL;
-- name
-- ----
-- Cy

Запомните: = NULL — почти всегда ошибка. Нужен IS NULL.

Ловушка 3: агрегаты и NULL

Агрегатные функции игнорируют NULL — и это иногда удобно, иногда коварно.

-- amounts: 100, 200, NULL
SELECT
    count(*)      AS rows_total,    -- 3: count(*) считает ВСЕ строки
    count(amount) AS amount_count,  -- 2: count(столбец) пропускает NULL
    sum(amount)   AS total,         -- 300: NULL не участвует в сумме
    avg(amount)   AS average;       -- 150: среднее по 2 значениям (300/2), НЕ 100!

avg(amount) равно 150, а не 100. Потому что avg = sum делить на count(amount) = 300 / 2. Строка с NULL не считается за «ноль» — она просто исключена из расчёта. Если бы NULL считался нулём, среднее было бы 100. Это различие — частый источник расхождения цифр в отчётах.

Управление NULL: COALESCE, NOT NULL, проектирование

Три инструмента, которыми junior-инженер закрывает 90% работы с NULL.

COALESCE возвращает первый не-NULL аргумент из списка — способ «подставить значение по умолчанию вместо NULL»:

SELECT name, COALESCE(phone, 'не указан') AS phone FROM users;
-- если phone IS NULL, в выдаче будет строка 'не указан'

NOT NULL constraint запрещает NULL в столбце на уровне схемы — лучшая защита: если значение всегда обязано быть, NULL туда просто не попадёт.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,        -- PRIMARY KEY уже подразумевает NOT NULL
    email   TEXT NOT NULL,              -- email обязателен — NULL запрещён
    phone   TEXT                        -- phone опционален — NULL разрешён
);

Проектирование. Само наличие NULL в столбце — это решение модели. Если столбец часто бывает NULL, потому что атрибут применим не ко всем строкам (вес посылки только для физических товаров), это сигнал: возможно, эти строки — отдельная сущность, и атрибут стоит вынести в отдельную таблицу. Тогда NULL не нужен вовсе.

NULL в ключах и связях: забегая вперёд

Два следствия трёхзначной логики, которые встретятся в следующих модулях, стоит зафиксировать уже сейчас.

Первое — NULL и первичный ключ. Primary key не может содержать NULL ни в одном столбце, и теперь понятно почему механически. Ключ обязан уникально идентифицировать строку, а уникальность проверяется сравнением. Если бы в ключе был NULL, то NULL = NULL дало бы UNKNOWN — СУБД не смогла бы достоверно сказать, совпадают два ключа или нет, и гарантировать уникальность стало бы невозможно. Поэтому стандарт SQL прямо требует от primary key NOT NULL. Модуль про ключи вернётся к этому.

Второе — NULL в foreign key. Здесь, наоборот, NULL разрешён, и он несёт смысл «связи нет». Foreign key со значением NULL означает, что строка ни на что не ссылается — например, у сотрудника без руководителя поле manager_id равно NULL. Это корректное состояние, и модуль про связи покажет, что NULL-допустимость foreign key — это способ выразить необязательное участие в связи.

Общий вывод: NULL — не universal-зло и не universal-благо. В одних местах модели он запрещён по фундаментальным причинам (ключи), в других — это рабочий инструмент (опциональные связи и атрибуты). Грамотный проектировщик решает осознанно для каждого столбца, а не ставит NULL «по умолчанию» или запрещает «на всякий случай».

Попробуй сам

Создайте таблицу employees(emp_id, name, salary, bonus) и вставьте 5 строк так, чтобы у 2 сотрудников bonus был NULL.

1. Выполните SELECT * FROM employees WHERE bonus > 0 и WHERE bonus = 0 и WHERE bonus < 0. Сложите число строк. Оно меньше 5 — объясните, какие строки и почему пропали.
2. Найдите сотрудников без бонуса. Сначала попробуйте WHERE bonus = NULL (вернёт пусто), затем WHERE bonus IS NULL (вернёт нужных). Проговорите разницу.
3. Сравните sum(salary + bonus) и sum(salary) + sum(bonus) — для строк с NULL-бонусом salary + bonus даст NULL, и результаты разойдутся. Почему?
4. Перепишите запрос пункта 3 через COALESCE(bonus, 0), чтобы NULL трактовался как нулевой бонус, и получите корректную сумму выплат.