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

В этом руководстве показано, как именно ведет себя NULL. Мы выполнили каждый запрос в SQLite 3.50, и показанные результаты получены в результате этого запуска. Большая часть такого поведения соответствует стандартному SQL, и мы отмечаем, где базы данных различаются, например порядок сортировки NULL и обработка пустых строк в Oracle.

Что такое НУЛЬ?

NULL — это маркер, означающий, что значение отсутствует или неизвестно. Это не значение в обычном понимании, поэтому это разные вещи:

Разница особенно важна для сумм. Бонус 0 означает, что человек ничего не получил. НУЛЕВОЙ бонус означает, что никто еще не знает.

Пример таблицы

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Обратите внимание, что у Сары есть пустая строка для телефона, а у Арджуна и Рави — NULL. К этим двум относятся совершенно по-разному.

Тестирование на NULL

Вот ошибку, которую поначалу допускают почти все:

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

Первый запрос является действительным SQL и не выдает ошибок. Он просто ничего не возвращает, потому что сравнение чего-либо с NULL никогда не является истинным. Сара появляется под IS NOT NULL, поскольку пустая строка является реальным значением. Однако в Oracle пустая строка рассматривается как NULL, что является заметным исключением.

Трехзначная логика

В большинстве языков программирования условие является истинным или ложным. SQL добавляет третий результат: неизвестно. Если вы не знаете значение, вы не можете сказать, равно ли оно 5. И неизвестно, что два неизвестных значения равны, поэтому NULL = NULL неверно. В нашем тесте все они вернули NULL:

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

Комбинирование условий следует строгим правилам. Вот результаты нашего прогона, где 1 — правда, а 0 — ложь:

Строки, которые исчезают из WHERE

WHERE сохраняет строку, только если ее условие true. Строки, результат которых неизвестен, удаляются вместе со строками, имеющими ложные значения. Это приводит к удивительным результатам:

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Вы могли бы ожидать появления Арджуна и Рави, поскольку их телефон определенно не с этим номером. Но их телефон неизвестен, поэтому результат неизвестен, и они исчезают. Чтобы включить их, скажите об этом явно: WHERE phone <> '555-0101' OR phone IS NULL. Отрицание тоже не помогает:

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

У Сары и Рави нет бонусов, поэтому они не появляются ни в bonus > 400, ни в его противоположности. Это ключевой момент, который следует помнить: условие и его отрицание не всегда охватывают каждую строку.

COALESCE: обеспечить запасной вариант

COALESCE возвращает первое значение, отличное от NULL, в своем списке и работает во всех основных базах данных:

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

Сара по-прежнему отображается пустой, поскольку пустая строка не равна NULL. Подобные функции существуют, например IFNULL в MySQL и SQLite, ISNULL в SQL Server и NVL в Oracle. COALESCE — портативный вариант. Наше предыдущее замечание о расчетах применимо и здесь. bonus + 100 дает NULL для всех, у кого нет бонуса, поэтому напишите COALESCE(bonus, 0) + 100, если считать недостающий бонус нулевым для вашего бизнеса.

NULLIF: превратить значение в NULL

NULLIF(a, b) возвращает NULL, если два значения равны, в противном случае возвращает a. Два варианта использования особенно полезны. Он может очищать значения заполнителей:

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

Этот второй запрос — удобный способ найти значения, которые «отсутствуют или пусты». Он также может защитить от деления на ноль, которое вызывает ошибку во многих базах данных:

SELECT total / NULLIF(quantity, 0) FROM sales;

Когда quantity равно 0, это дает NULL, а не ошибку. В нашем тесте SELECT 100 / NULLIF(0, 0) вернул NULL.

Агрегаты пропускают NULL

Именно здесь NULL наносит наибольший ущерб отчетам:

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

Так какой же средний бонус: 400 или 200? Это зависит от того, что означает NULL в ваших данных. Если NULL означает «бонус не был выплачен», то верно значение 200. Если это означает «еще не решено», то 400, возможно, и правы. База данных не может принять это решение за вас, поэтому вопрос должен быть адресован тому, кто разбирается в бизнесе.

Связанная ловушка: SUM без значений имеет значение NULL, а не 0. Когда мы суммировали только строки без бонуса, мы получили NULL, а COUNT вернул 0. Код приложения, ожидающий числа, может привести к сбою на этом, поэтому оберните его: COALESCE(SUM(bonus), 0).

ГРУППИРОВАТЬ ПО, сортировать и РАЗЛИЧАТЬ

Хотя значения NULL «не равны» при сравнении, эти операции рассматривают их как одну группу:

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

Дополнительную информацию о группировке см. в разделе SQL GROUP BY и HAVING. Порядок сортировки — это то, где базы данных действительно различаются. SQLite и MySQL рассматривают NULL как наименьшее значение, а PostgreSQL и Oracle — как наибольшее. Таким образом, тот же ORDER BY bonus DESC может помещать NULL последними в один и первым в другой. PostgreSQL, Oracle и современный SQLite позволяют вам заявить об этом напрямую, используя NULLS FIRST или NULLS LAST.

Присоединяется

Условие соединения с использованием = никогда не соответствует NULL, поэтому строки с ключом NULL исключаются из внутреннего соединения:

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

У Миры нет менеджера, поэтому внутреннее соединение автоматически удаляет эту строку. LEFT JOIN сохраняет его, а также создает собственные NULL для недостающей стороны. Подробнее читайте в объяснениях SQL-соединений.

Ловушка «НЕ В»

Один NULL в списке NOT IN уничтожает все результаты:

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

Вторая форма означает manager_id <> 1 AND manager_id <> NULL, и эта последняя часть никогда не может быть правдой. Это особенно опасно для подзапросов, где NULL может прийти незамеченным. Вместо этого используйте NOT EXISTS, как описано в нашем руководстве по подзапросам SQL. Мира также отсутствует в первом результате, поскольку manager_id этой строки имеет значение NULL.

Проектирование с NULL

• Используйте NOT NULL по умолчанию. Разрешите NULL только тогда, когда значение действительно может отсутствовать.
• Добавьте разумные значения по умолчанию. Например, quantity INT NOT NULL DEFAULT 0.
• Не используйте магические значения. Дата 1900-01-01 или возраст -1, заменяющий «неизвестно», испортят ваши расчеты.
• Знайте поведение UNIQUE. Большинство баз данных допускают наличие нескольких NULL в столбце UNIQUE, и наш тест вставил два значения без ошибок. SQL Server является исключением, поскольку обычно это допускается.
• Помните, что первичный ключ не может иметь значение NULL. См. первичный ключ и внешний ключ.
• Тестирование с NULL. Тестовые данные без NULL скрывают эти ошибки. Включите их намеренно, как и другие крайние случаи.

Итоги

Относитесь к NULL как к «неизвестному», и большая часть его поведения обретает смысл: неизвестно плюс один неизвестно, и неизвестное равно неизвестному, неизвестно, что оно истинно. На этой неделе проведите аудит одного из своих отчетов. Сравните столбцы с нулевым значением, используя COUNT(*) и COUNT(column), найдите любой NOT IN и решите, что NULL должен означать в каждом среднем значении.

Часто задаваемые вопросы

Продолжайте учиться