SQLite и NULL: IS NULL, COALESCE, IFNULL на практике

NULL означает «неизвестно»

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

Создадим небольшую таблицу для экспериментов:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT * FROM users;

Два столбца допускают NULL. У Бориса нет email. У Клео не указан возраст. У Дэна нет ни того, ни другого. Дальше речь пойдёт о том, как корректно запрашивать такие строки и не попасться в ловушку.

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

Первое, что хочется написать - WHERE email = NULL. Выглядит логично. И возвращает пустоту:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email = NULL;

Ноль строк - хотя у Бориса и Дана почта явно NULL. Дело вот в чём: любое сравнение с NULL даёт в результате NULL, а не true или false. А WHERE в SQLite пропускает только те строки, где условие именно true - NULL под это не подходит, поэтому строка отбрасывается. Вот почему = NULL в SQLite не работает.

Та же ловушка и с <>:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email FROM users WHERE email <> 'ada@example.com';

Казалось бы, запрос должен вернуть всех, кроме Ады. А возвращает только Клео. Борис и Дэн, у которых email равен null, выпадают - потому что NULL <> 'ada@example.com' тоже даёт NULL, а не true.

Это самая распространённая засада в SQL. Если запрос внезапно «теряет строки», которых вы там ожидали, - первым делом подозревайте колонку с null.

Используйте IS NULL и IS NOT NULL

Правильный способ проверки на null в SQLite - оператор IS. В отличие от =, он умеет работать с null и всегда возвращает true или false, но никогда не null:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name FROM users WHERE email IS NULL;

SELECT name FROM users WHERE email IS NOT NULL;

Первый запрос вернёт Бориса и Дэна, второй - Аду и Клео. Операторы IS NULL и IS NOT NULL как раз и созданы для того, чтобы задать вопрос «а это значение вообще есть?». Используйте их везде, где рука так и тянется написать = NULL или <> NULL - это типичная ошибка проверки на NULL в SQLite.

Если же нужно получить «все, кроме Ады, включая записи с неизвестным именем», объедините обе проверки явно:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, email
FROM users
WHERE email <> 'ada@example.com' OR email IS NULL;

Теперь в выборку попадают Борис, Клео и Дэн.

NULL заражает арифметику и конкатенацию

Правило «неизвестно» работает не только в сравнениях. Любая операция, в которой замешан NULL, даёт на выходе NULL:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    age,
    age + 1       AS next_year,
    age * 2       AS doubled,
    'Привет, ' || name AS greeting,
    'Возраст: ' || age AS labelled_age
FROM users;

next_year и doubled равны NULL у Клео и Дэна. labelled_age у них тоже NULL - конкатенация строки с NULL даёт NULL, а не 'Возраст: '. Если в колонке может оказаться NULL, а на выходе нужно нормальное значение, это нужно обрабатывать вручную. Как раз для этого пригодятся следующие две функции.

IFNULL: подстановка значения по умолчанию

IFNULL(a, b) возвращает a, если оно не NULL, иначе - b. Это самый простой способ заменить NULL на дефолтное значение:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    IFNULL(email, '(нет email)') AS email,
    IFNULL(age, 0)               AS age
FROM users;

Борис и Дэн получат (нет email). Клео и Дэн получат 0. Исходные данные при этом не меняются - IFNULL лишь переписывает то, что выводится.

У IFNULL всегда ровно два аргумента. Если запасных вариантов нужно больше - берите COALESCE.

COALESCE: побеждает первое не-NULL значение

COALESCE(a, b, c, ...) перебирает аргументы по порядку и возвращает первый, который не равен NULL. По сути, это IFNULL с любым количеством запасных значений:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    name,
    COALESCE(email, name || '@unknown.local', 'аноним') AS contact
FROM users;

Для Ada и Cleo подставляется реальный email. У Boris и Dan поле email равно NULL, поэтому SQLite берёт второй аргумент - адрес, собранный из имени. Если бы и он оказался NULL, сработал бы запасной вариант - 'аноним'.

COALESCE - это переносимый вариант: все крупные СУБД поддерживают его одинаково. IFNULL - это удобное сокращение из SQLite и MySQL, но только для случая с двумя аргументами. По умолчанию используйте COALESCE, а к IFNULL обращайтесь, только когда аргументов действительно два и хочется коротко.

NULL и пустая строка в SQLite - это не одно и то же

Частая путаница: разработчики считают, что NULL и '' взаимозаменяемы. Это не так.

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT
    '' IS NULL          AS empty_is_null,
    NULL IS NULL        AS null_is_null,
    length('')          AS empty_length,
    length(NULL)        AS null_length,
    '' = ''             AS empty_equals_empty,
    NULL = NULL         AS null_equals_null;

'' - это настоящая строка, просто без символов. А NULL - это вообще отсутствие значения. length('') вернёт 0, тогда как length(NULL) сам по себе будет NULL. И NULL = NULL - это тоже NULL, а вовсе не 1. Именно поэтому в SQLite и существует конструкция IS NULL, а проверка через = NULL не работает.

Если в колонке могут оказаться и '', и NULL, заранее решите, что у вас будет означать «значения нет», и придерживайтесь одного варианта. Иначе в каждом запросе придётся учитывать оба случая - и рано или поздно вы про один из них забудете.

NULL в IN, NOT IN и DISTINCT

Есть ещё пара мест, где NULL подкрадывается незаметно.

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

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT name, age FROM users WHERE age NOT IN (25, NULL);

Казалось бы, должны вернуться все, у кого возраст не равен 25. Но в ответе - пусто. SQLite разворачивает NOT IN (25, NULL) примерно в age <> 25 AND age <> NULL, а выражение age <> NULL всегда даёт NULL - поэтому всё условие никогда не становится истинным. Лечится это так: нужно отфильтровать NULL либо из самого списка, либо из столбца до сравнения. Именно поэтому = NULL в SQLite не работает - для проверки на NULL всегда используйте IS NULL и IS NOT NULL.

А вот DISTINCT, наоборот, при удалении дубликатов считает NULL-значения равными друг другу:

CREATE TABLE users (
    id     INTEGER PRIMARY KEY,
    name   TEXT NOT NULL,
    email  TEXT,
    age    INTEGER
);

INSERT INTO users (name, email, age) VALUES
    ('Ada',   'ada@example.com', 30),
    ('Boris', NULL,              25),
    ('Cleo',  'cleo@example.com', NULL),
    ('Dan',   NULL,              NULL);

SELECT DISTINCT email FROM users;

Получаешь три строки: email Ады, email Клео и одно NULL (сюда схлопнулись Борис и Дэн). С GROUP BY и UNION та же история - они считают все null'ы одной группой, то есть ведут себя ровно наоборот по сравнению с =. В SQL логика тут не везде одинаковая, так что полезно держать в голове, на какой стороне находится каждый оператор.

Короткий чек-лист

• Для проверки на NULL в SQLite используй IS NULL / IS NOT NULL. Никогда не пиши = NULL.
• Любая арифметика, конкатенация или сравнение с участием NULL вернёт NULL.
• Чтобы подставить значение по умолчанию, бери COALESCE(a, b, c, ...). Для случая с двумя аргументами есть короткая форма - IFNULL(a, b).
• Пустая строка '' - это не NULL. Для каждой колонки выбери что-то одно как обозначение «нет значения».
• NOT IN (..., NULL) - почти всегда баг. Сначала выкидывай null'ы из списка.

Дальше: сортировка результатов

Когда фильтрация строк под контролем - включая строки с null, - пора заняться порядком вывода. На следующей странице разберём ORDER BY, и у него тоже есть свои взгляды на то, где должны оказаться null'ы в отсортированной выдаче.