Еще о NULL-значениях

Смысл NULL-значения — это отсутствие информации или неприменимость данного атрибута в данном кортеже.

Можно спросить: «Зачем иметь атрибут, если его значение неприменимо?». Ответ лежит в области моделирования предметной области. Рассмотрим, например, схему базы данных «Компьютеры». Она представляет собой реляционную модель связи «тип-супертип». Сущностями предметной области здесь являются модели компьютерной продукции (супертип), при этом каждый тип продукции (ПК, ноутбук или принтер) отображается в отдельную таблицу со связями «многие к одному» с таблицей Product.

Такая модель обеспечивает высокую степень нормализации (3НФ). Однако это не единственный способ. Можно было бы хранить всю информацию в одной таблице, которая содержала бы как общие для всех моделей атрибуты (например, цена — price), так и атрибуты, которые имеют смысл только для моделей определенного типа (например, цвет — color — для характеристики принтеров). Для такой схемы NULL-значение является вполне оправданным именно в смысле неприменимости характеристики, то есть NULL в столбце color, будет говорить нам о том, что эта характеристика не имеет отношения, скажем, к моделям ПК.

Обратимся теперь ко второй ипостаси NULL-значений — отсутствию информации. Если мы решим отказаться от использования NULL-значений, то должны предложить альтернативу. Естественным путем является применение значения по умолчанию, которое будет подставлено в соответствующий столбец при отсутствии информации. Следует заметить, что таких значений по умолчанию должно быть, по меньшей мере, столько, сколько различных типов данных поддерживается СУБД (целые, строки, дата-время, …).

Рассмотрим, например, таблицу Laptop и поле price (цена). Пусть предметная область такова, что на момент ввода информации о моделях ноутбуков их цена не всегда известна. При выборе значения по умолчанию мы должны ограничиться только значениями, допустимыми для поля price. Тип данных для столбца (money) заставляет нас выбирать только из числовых значений, совместимых с данным типом и проверочными ограничениями (ограничение типа CHECK), наложенными на допустимые значения для этого столбца. Любое положительное значение в качестве значения по умолчанию будет вызывать путаницу, так как невозможно будет отличить «истинное» значение цены от заменителя отсутствующей цены. Поэтому следует выбрать нуль или любое отрицательное значение. А теперь поговорим о том, чем плоха такая замена.

Для примера рассмотрим информацию о моделях 1298, имеющихся в таблице Laptop. Чтобы познакомиться с данными, выполним запрос:

SELECT *
FROM Laptop
WHERE model = 1298;

Вот результаты выполнения этого запроса:

Рассмотрим задачу получения средней цены модели 1298. Пока все цены известны решение этой задачи не вызывает никаких сомнений:

SELECT model, AVG(price) avg_price
FROM Laptop
WHERE model = 1298
GROUP BY model;

Пусть теперь цена модели с кодом 4 неизвестна. Если, как было решено ранее, мы будем заменять неизвестное значение, скажем, нулем (UPDATE Laptop SET price = 0 WHERE code=4), то получим заведомо неверное среднее значение цены — 550.0

Если же мы будем использовать NULL-значение, то результат будет вполне правильным — 825.0, так как NULL-значения будут игнорироваться при использовании агрегатной функции, в результате чего среднее значение будет вычисляться только по моделям с известной ценой (то есть среднее по двум моделям).

Итак, как мы постарались показать, NULL-значение является неотъемлемой особенностью реляционной модели, поэтому рекомендуем научиться корректно работать с такими значениями.

Сравнение строк, содержащих NULL-значения

Как известно, предикаты простого сравнения с NULL-значениями дают истинностное значение UNKNOWN, т.е. ни TRUE и ни FALSE, что означает “неизвестно”. Поэтому не стоит удивляться, что в одних случаях, при сравнении между собой NULL-значений, они считаются равными друг другу, а других случаях - нет. Поясним сказанное на примерах.

Рассмотрим соединение двух одинаковых строк, содержащих NULL-значения, по равенству всех столбцов.

with A as 
(
select 'a' a, null b
)
, B as 
(
select 'a' a, null b
)
select * 
from A 
    join B ON A.a=B.a 
          and A.b=B.b;

В этом случае соединяться будут только те строки, для которых предикат соединения есть TRUE. Поскольку предикат соединения в примере оценивается как UNKNOWN, в результате мы не получим ни одной строки.

Однако пересечение запросов (так же, как объединение и разность) считает эти строки идентичными:

with A as 
(
select 'a' a, null b
)
, B as 
(
select 'a' a, null b
)
select * from A
INTERSECT
select * from B;

Можно сделать вывод о том, что при горизонтальных операциях NULL-значения не считаются равными (и неравными, впрочем, тоже), а при вертикальных трактуются как равные. В частности, при группировке по столбцу, содержащему NULL-значения, последние образуют одну группу.

В заключение рассмотрим несколько решений задачи определения количества принтеров с неизвестной ценой. Таблица PrinterN отличается от таблицы Printer тем, что для пары моделей цена установлена в NULL.

(1) От общего числа строк отнимем число строк с известной ценой.

select count(*) - count(price) qty from printerN;

(2) Использование предиката IS NULL для подсчёта строк, для которых цена неизвестна.

select count(*) from printerN where price IS NULL;

(3) Группируем по цене и отбираем группу с неизвестной ценой, используя HAVING.

select count(*) from printerN group by price having price IS NULL;