NULL и трёхзначная логика

NULL — главный источник «магических» багов в SQL. Разбираем трёхзначную логику и ловушку NOT IN.

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

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

Сравнение NULL = NULL возвращает не TRUE, а UNKNOWN — ведь «неизвестно» не равно «неизвестно». Поэтому WHERE col = NULL никогда не вернёт строк. Для проверки на NULL есть отдельные операторы IS NULL и IS NOT NULL.

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

SELECT
    (NULL = NULL)   AS ravno_null,      -- UNKNOWN -> в выводе пусто
    (NULL <> 1)     AS ne_ravno,        -- тоже UNKNOWN
    (1 = 1)         AS istina;          -- 1 (TRUE)

Вывод:

||1

Колонки ravno_null и ne_ravno пусты — это NULL/UNKNOWN. Сравнение с NULL не даёт ни истины, ни лжи.

Главная ловушка: NOT IN со списком, где есть NULL

Это любимый вопрос на собеседовании. Запрос «выбрать тех, кого нет в списке» через NOT IN внезапно возвращает пусто, если в списке затесался NULL.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Аня'), ('Борис'), ('Вера');

CREATE TABLE banned (name TEXT);
INSERT INTO banned (name) VALUES ('Борис'), (NULL);   -- внимание: NULL в списке

-- Хотим: все, кроме забаненных. Но NOT IN с NULL ломается:
SELECT name
FROM users
WHERE name NOT IN (SELECT name FROM banned);

Вывод:

Результат пуст, хотя мы ждали Аню и Веру! Причина: name NOT IN (Борис, NULL) разворачивается в name <> Борис AND name <> NULL. Второе сравнение даёт UNKNOWN, и всё AND-выражение никогда не становится TRUE.

Как чинить: NOT EXISTS

Надёжный способ — NOT EXISTS, он корректно работает с NULL:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO users (name) VALUES ('Аня'), ('Борис'), ('Вера');

CREATE TABLE banned (name TEXT);
INSERT INTO banned (name) VALUES ('Борис'), (NULL);

SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM banned b WHERE b.name = u.name
)
ORDER BY u.name;

Вывод:

Аня
Вера

Теперь корректно: Аня и Вера. NOT EXISTS проверяет наличие совпадения построчно и не страдает от NULL в списке. Альтернатива — отфильтровать NULL: WHERE name NOT IN (SELECT name FROM banned WHERE name IS NOT NULL).

Итог

• NULL означает «неизвестно»; сравнение с ним даёт UNKNOWN, а не TRUE/FALSE.
• Проверяйте на NULL только через IS NULL / IS NOT NULL.
• NOT IN со списком, содержащим NULL, вернёт пусто — используйте NOT EXISTS или отфильтруйте NULL.