Отсутствующие значения

Глава из книги Кена Хендерсона: Профессиональное руководство по Transact-SQL. Для профессионалов

Авторы блога благодарят издательским домом ПИТЕР за предоставленные к публикации материалы.

Аннотация издательства

Кен Хендерсон хотел написать самую лучшую из возможных книг о практическом программировании на Transact-SQL. И ему это удалось. «Профессиональное руководство по Transact-SQL» будет для вас чем-то вроде путеводителя. Эту книгу легко читать, в ней много новых интересных идей и примеров кода. Хотя она посвящена исключительно Transact-SQL, вы увидите, что многие из подходов и методов будут работать с любым SQL-продуктом, ведь здесь описывается разработка высококачественного кода, независимо от его применения. Автор поставил перед собой задачу написать не только полезную, но и интересную книгу для программистов, в которой не было бы формализмов и от которой не веяло бы нафталином. Кен Хендерсон уверен, что технический писатель должен писать так, как говорят читатели. Так что читайте и наслаждайтесь!

Введение

Из тридцати шести альтернатив лучшей является побег. (Китайская пословица)

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

Существует, по крайней мере, три различные научные школы обработки отсутствующих значений. Изобретатель реляционных баз данных, д-р  Е. Ф. Кодд пропагандирует две различные лексемы для представления отсутствующих значений: одну для представления значений, которые должны быть, но их нет (например, пол человека), и одну для значений, которых вообще не должно быть, так как они неприменимы (например, пол организации). Крис Дейт, известный специалист по базам данных, принял позицию минималистов. Он считает, что в SQL вообще не должно быть лексемы для представления отсутствующих значений. ANSI/ISO SQL-92 идет на компромисс и предоставляет одну универсальную лексему для отсутствующих значений: NULL.

Рискуя показаться банальным, отмечу, что отсутствующие и пустые значения - это две большие разницы. Целое, значение которого отсутствует, это не то же самое, что целое, значение которого равно нулю. Неопределенная строка - это не то же самое, что строка нулевой длины или строка, содержащая одни пробелы. Эта разница важна, так как сравнения между пустыми и неопределенными значениями всегда будут неудачны. Фактически, NULL-значения даже не равны друг другу в таких сравнениях.

Возможность неопределенных значений в реляционных базах данных означает, что для любого сравнения возможны три результата: Истина (True), Ложь (False) или Неизвестно (Unknown). Конечно, это требует использования трехзначной логики. Это показано в таблице истинности, представленной на рис. 3.1.

Обратите внимание, что я использую NULL и Unknown, считая их взаимозаменяемыми, хотя с технической точки зрения это не так. NULL - значение данных, тогда как Unknown представляет логическое значение. Эта разница немного трудна для понимания - особенно для разработчиков-ветеранов, - и это причина, по которой вы должны использовать ...WHERE столбец IS NULL, вместо ...WHERE столбец = NULL, если хотите, чтобы SQL вел себя разумно. (Transact-SQL не запрещает такой синтаксис, но, поскольку одно значение NULL никогда не равно другому - даже самому себе, он никогда не вернет True. Смотрите раздел ниже о работе с ANSI NULL в Transact-SQL.) Каким бы смешным это ни показалось, я не хочу пускаться в философские рассуждения относительно NULL-значений и их правильного использования. Поскольку наша задача состоит в том, чтобы увидеть мир данных и баз данных глазами Transact-SQL, я всюду в книге буду считать значения NULL и Unknown тождественными.

AND	True	False	Unknown
True	True	False	Unknown
False	False	False	False
Unknown	Unknown	False	Unknown
OR	True	False	Unknown
True	True	True	True
False	True	False	Unknown
Unknown	True	Unknown	Unknown
NOT	True	False	Unknown
	False	True	Unknown

Рис. 3.1. Таблицы истинности трехзначной логики

NULL и выражения

Как правило, применение NULL-значения в выражении приводит к результату, равному NULL. Например, SELECT 5+NULL вернет NULL, а не 5, так же как и SELECT SUBSTRING('Groucho', 3, 2+NULL).

Исключением из этого правила являются агрегатные функции.

Также NULL-значения никогда не равны друг другу; фактически NULL-значения даже не равны сами себе, что проиллюстрировано в следующем запросе:

CREATE TABLE #nulltest (c1 int NULL) GO INSERT #nulltest VALUES (1) INSERT #nulltest VALUES (NULL) INSERT #nulltest VALUES (3) GO DECLARE @nv int SELECT @nv=c1 FROM #nulltest WHERE c1 IS NULL -- Получаем NULL из второй записи SELECT MyNV=c1 FROM #nulltest WHERE c1=@nv -- Не вернет ни одной записи

NULL и функции

Как и в случае простых выражений, при передаче большинству функцифункций NULL-значений результатом будет NULL, так что SELECT SIGN(NULL) вернет NULL, так же как и SELECT ABS(NULL) и SELECT LTRIM(NULL). Исключение составляют функции, специально предназначенные для работы с неопределенными значениями. В дополнение к агрегатным функциями, это функции ISNULL() и COALESCE().

ISNULL() преобразует NULL-значение к значению, отличному от NULL. Например,

SELECT ISNULL(c1,0) FROM #nulltest

преобразует все NULL-значения в столбце c1 к 0. Этот подход нужно использовать осторожно, потому что преобразование NULL к другим значениям может иметь побочные эффекты. Например, запрос с AVG в следующем примере не сможет проигнорировать преобразованные NULL-значения:

SELECT AVG(ISNULL(c1,0)) FROM #nulltest

Нулевые значения применяются при вычислении среднего, сильно понижая его значение. Заметьте, что параметры ISNULL() не ограничены константами. Рассмотрим пример:

DECLARE @x int,@y int SET @x=5 SET @y=2 SELECT ISNULL(CASE WHEN @x>=1 THEN NULL ELSE @x END, CASE WHEN @y<5 THEN @x*@y ELSE 10 END)

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

ISNULL() в качестве параметра поддерживает даже оператор SELECT, как здесь:

DECLARE @x int,@y int SET @x=5 SET @y=2 SELECT ISNULL(CASE WHEN @x>=1 THEN NULL ELSE @x END, (SELECT COUNT(*) FROM authors))

Функция NULLIF() - противоположность ISNULL(). Хотя она и не обрабатывает переданные ей NULL-значения лучше любой другой функции, она была разработана для возвращения NULL-значения в правильных ситуациях. Она принимает два параметра и возвращает NULL, если они равны; иначе она возвращает первый параметр. Например,

DECLARE @x int,@y int SET @x=5 SET @y=2 SELECT NULLIF(@x,@y+3)

возвращает NULL, тогда как SELECT NULLIF(@x, @y) вернет 5.

COALESCE() возвращает первое значение из горизонтального списка, не равное NULL:

SELECT COALESCE(@x / NULL, @x * NULL, @x+NULL, NULL, @y*2, @x, (SELECT COUNT(*) FROM authors))

вернет @y*2 или 4. Как и в случае ISNULL(), параметрами COALESCE() могут быть выражения и подзапросы, а также константы, как было показано в примерах.

NULL и ANSI SQL

С каждой успешной версией SQL Server все полнее соответствует стандартам ANSI/ISO. Используя множество настроек конфигурации и современный синтаксис команд, вы можете писать Transact-SQL, переносимый на другие ANSI-совместимые СУБД.

NULL-значения представляют собой область, в которой соответствие ANSI значительно улучшилось в версии 7.0. Для увеличения совместимости было добавлено множество новых параметров настройки конфигурации и опций синтаксиса. Многие из них мы обсудим далее.

Что касается обработки неопределенных значений в выражениях, спецификация ANSI/ISO SQL корректно разделяет агрегацию и просто вычисление выражений (это противоречит информации в нескольких других замечательных книгах по SQL). Это означает, что в соответствии со стандартом, добавление NULL-значения к числу - это не то же самое, что вычисление агрегатного значения агрегатное значениестолбца, который содержит и NULL и не-NULL-значения. В первом случае результатом всегда будет неопределенное значение. Во втором NULL-значения будут проигнорированы и агрегация будет осуществлена. В соответствии со спецификацией ANSI, агрегатная функция может вернуть NULL, только если таблица пуста или не содержит ничего, кроме NULL-значений в агрегируемом столбце (COUNT() представляет собой исключение - смотрите ниже). Так как в этом случае Transact-SQL следует стандарту, эти положения также применимы и к нему. Например, рассмотрим таблицу из начала главы:

CREATE TABLE #nulltest (c1 int NULL)

и следующие данные:

INSERT #nulltest VALUES (1) INSERT #nulltest VALUES (NULL) INSERT #nulltest VALUES (3)

Запрос:

SELECT AVG(c1) FROM #nulltest

не возвращает NULL, даже при том, что одно из обрабатываемых им значений действительно равно NULL. Вместо этого NULL игнорируется при вычислении среднего значения, что нам как раз и требуется. Так же дела обстоят и с функциями SUM(), MIN() и MAX(), но не в случае COUNT(*).

Например,

SELECT COUNT(*) FROM #nulltest

вернет 3, так что SELECT SUM(c1)/COUNT(*) - это не то же самое, что SELECT AVG(c1). COUNT(*) подсчитывает записи, не обращая внимания на неопределенные значения. Она включает вторую запись таблицы даже при том, что таблица имеет всего один столбец и значение этого столбца во второй записи - NULL. Если вы хотите, чтобы поведение этой функции было таким же, как и у других агрегатных функций, укажите вместо "*" столбец таблицы (например, COUNT(c1)). Этот синтаксис корректно игнорирует неопределенные значения, так что SELECT SUM(c1)/COUNT(c1) возвращает такое же значение, что и SELECT AVG(c1).

Это тонкое различие между COUNT(*) и COUNT(c1) очень важно, так как они возвращают различные результаты, когда появляются неопределенные значения. Вообще, лучше применять COUNT(*) и позволить оптимизатору выбрать наилучший способ возращения количества записей, чем заставлять его подсчитывать конкретный столбец. Если вам необходимо "специальное" поведение COUNT(c1), разумно отразить причины этого в комментариях в вашем коде.

По умолчанию, если вы соединяетесь с сервером посредством ODBCODBC или OLEDBOLEDB, установка SQL Server ANSI_WARNINGS включена. Это означает, что сервер будет генерировать предупреждающие сообщения для всех запросов, в которых неопределенные значения игнорируются агрегатами. Здесь не о чем волноваться, если вы знаете о своих неопределенных значениях и они должны игнорироваться, однако в другом случае эти сообщения могут предупредить вас о проблемах с данными.

ANSI_WARNINGS может быть установлена глобально для заданной базы данных с помощью sp_dboption или для сессии с помощью команды SET ANSI_WARNINGSSET ANSI_WARNINGS, команда. Как и в случае других опций базы данных, значения настроек сессии перекрывают значения настроек базы данных.

Другие важные настройки, связанные с обработкой ANSI NULL, - SET ANSI_ NULL_DFLT_ON/_OFF, SET ANSI_NULLS и SET CONCAT_NULL_YIELDS_NULL.

SET ANSI_NULL_DFLT_ON/_OFF определяет, могут ли столбцы в новой таблице по умолчанию содержать NULL-значения. Вы можете получить значение этой настройки с помощью системной функции GETANSINULL().

SET ANSI_NULLS SET ANSI_NULLSконтролирует, как работает сравнение на равенство с NULL. В стандарте ANSI SQL предусмотрено, что любое выражение, содержащее операторы сравнения оператор;сравнения(=, <>, >= и так далее - "тета"-операторы, говоря языком Кодда) и NULL-значения, возвращает NULL. Отключение этой настройки (она включена по умолчанию при соединении с помощью ODBCODBC или OLEDB) разрешает возможность успешного сравнения на равенство с NULL.

SET CONCAT_NULL_YIELDS_NULL определяет, будет ли конкатенация строк с NULL возвращать NULL. Обычно SELECT "Rush Limbaugh's IQ="+NULL возвращает NULL, но это можно отключить с помощью команды Transact-SQL SET CONCAT_NULL_ YIELDS_NULL. Обратите внимание, что эта настройка не влияет на другие типы значений. Сложение NULL с числовым значением всегда возвращает NULL, независимо от CONCAT_NULL_YIELDS_NULL.

Я должен в этом месте упомянуть об одном моменте в стандарте SQL, который всегда казался мне противоречивым. Я нахожу немного неестественным тот факт, что вы можете присваивать значения столбцам с помощью =NULL, но не можете искать значения с помощью того же синтаксиса. Например,

UPDATE authors SET state=NULL WHERE state='CA'

сопровождаемый

SELECT * FROM authors WHERE state=NULL

не работает так, как вы могли бы ожидать. Оператор SELECT не вернет ни одной записи, даже когда несколько из них были только что установлены в NULL. То, что одно значение NULL не равно другому, представляет собой синтаксическую несогласованность. Я считаю, что стандарт мог бы быть более логичным, если бы присваивание осуществлялось таким образом:

UPDATE authors SET state TO NULL WHERE state='CA'

Если бы это было разрешено, запрет =NULL был бы разумнее, но, увы, это не так.

NULL и хранимые процедуры

Хранимые процедуры хранимые процедуры_- это то место, где особенно удобно контролировать поведение Transact-SQL, касающееся ANSI-совместимости. Рассмотрим следующую хранимую процедуру:

CREATE PROCEDURE ListIdsByValue @val int AS CREATE TABLE #values (k1 int identity, c1 int NULL) INSERT #values (c1) VALUES (1) INSERT #values (c1) VALUES (1) INSERT #values (c1) VALUES (NULL) INSERT #values (c1) VALUES (9) SELECT * FROM #values WHERE c1=@val DROP TABLE #values

Несмотря на то что во временной таблице есть записи, в которых столбец c1 равен NULL, если передать NULL в качестве единственного параметра, процедура не вернет ни одной записи, так как одно значение NULL никогда не равно другому. Конечно, в хранимой процедуре можно предусмотреть специальную обработку неопределенных значений, но этот подход очень быстро становится ненадежным по мере увеличения количества процедур с большим количеством параметров. Например, процедуре всего лишь с двумя параметрами, которые могут принимать неопределенные значения, потребуется оператор IF IF, операторс четырьмя уровнями вложенности, и код, необходимый для выполнения запроса, будет размножаться. Однако благодаря SET ANSI_NULLS SET ANSI_NULLSэто поведение можно изменить, вот так:

SET ANSI_NULLS OFF GO CREATE PROCEDURE ListIdsByValue @val int AS CREATE TABLE #values (k1 int identity, c1 int NULL) INSERT #values (c1) VALUES (1) INSERT #values (c1) VALUES (1) INSERT #values (c1) VALUES (NULL) INSERT #values (c1) VALUES (9) SELECT * FROM #values WHERE c1=@val DROP TABLE #values GO SET ANSI_NULLS ON GO

Тем самым мы изменяем жизнеспособность расширения =NULL Transact-SQL на период выполнения процедуры. Под "жизнеспособностью" я подразумеваю, что, помимо того что код не генерирует ошибки, синтаксис работает, как и предполагается. Хотя этот синтаксис технически правилен, независимо от SET ANSI_ NULLS, он никогда не вернет True, если включена совместимость с ANSI. Как вы можете предположить из этого кода, это расширение значительно упрощает обработку параметров хранимых процедур, которые могут принимать значение NULL, - вот основная причина, по которой эта конструкция была добавлена в язык.

Этот способ работает, потому что статус ANSI_NULLS записывается каждый раз при создании процедуры. Таким образом, мы получаем виртуальный снимок окружения, в котором процедура была создана, что позволяет управлять настройками, так, чтобы они не влияли ни на что другое. В заключение отметим, что независимо от текущего состояния ANSI_NULLS при запуске процедуры ее значение будет таким, каким оно было во время компиляции процедуры, и процедура будет вести себя соответственно, так что будьте внимательны. Например:

SET ANSI_NULLS OFF GO EXEC ListIdsByValue @val=NULL GO SET ANSI_NULLS ON GO

не вернет ни одной записи, если ANSI_NULLS не была установлена в OFF, когда процедура была откомпилирована. Учтите, что SET ANSI_NULLS также влияет на жизнеспособность синтаксиса IN (значение, значение, NULL). Это означает, что запрос, подобный этому:

SELECT * from #values where (c1 in (1, NULL))

не вернет записи с NULL-значениями, если только ANSI_NULLS не была отключена. Это становится понятным, если считать предикат IN короткой записью нескольких сравнений на равенство, объединенных по OR.

ПРИМЕЧАНИЕ

Я не поощряю ненужные отклонения от спецификации ANSI/ISO SQL. Всегда лучше писать код, соответствующий стандартам, независимо от синтаксических возможностей вашей разновидности SQL. ANSI/ISO-совместимый код позволяет переносить его на другие платформы, кроме того, он легче воспринимается другими людьми. Как и в случае использования неопределенных значений, вы должны несколько раз подумать, прежде чем писать код, отклоняющийся от установленных норм, особенно при одновременной работе с несколькими СУБД.

Используйте NULL-значения, если это необходимо

Как я упоминал ранее, я не собираюсь рассуждать о правильном использовании неопределенных значений. Однако об этом стоит упомянуть: на практике NULL-значения в реляционных базах данных могут стать серьезной проблемой. Это лучше показать на нескольких примерах. Предположим, что мы имеем следующую таблицу и данные:

CREATE TABLE #values (k1 int identity, c1 int NULL) INSERT #values (c1) VALUES (1) INSERT #values (c1) VALUES (1) INSERT #values (c1) VALUES (NULL) INSERT #values (c1) VALUES (9)

можно подумать, что запрос

SELECT * FROM #values WHERE c1=1

сопровождаемый

SELECT * FROM #values WHERE c1<>1

вернет все записи из таблицы #values, однако это не так. Помните, что SQL основан на трехзначной логике. Чтобы получить все записи, мы должны учесть неопределенные значения, так что необходимо что-то вроде

SELECT * FROM #values WHERE c1=1 OR c1 IS NULL

Все становится понятным, если рассматривать NULL во второй записи просто как метку-заполнитель. На самом деле значение столбца c1 во второй записи неизвестно, так что мы не можем однозначно сказать, равен столбец 1 или нет, следовательно, мы исключаем эту запись из обоих запросов. К сожалению, многие разработчики рассуждают иначе. Для большинства из них что-то либо есть, либо нет - третьего не дано. По этой причине неопределенные значения - проблема многих новичков, работающих с SQL.

Другая проблема с неопределенными значениями заключается в том, что большинство языков программирования не могут представить их корректно. Эта проблема отходит на второй план по мере увеличения использования типов данных OLE, но обычно в языках программирования нет предопределенных констант для симуляции неопределенных значенийсимуляция неопределенных значений, если они вообще поддерживают их.

Переменная, которой не присвоено значение, - это не то же самое, что переменная, содержащая NULL; если предположить, что это одно и то же, можно получить некорректные результаты. Также несколько серверов баз данных, не говоря уже о традиционных языках программирования, реализуют поведение NULL полностью в соответствии с ANSI SQL, и разница между способами обработки NULL-значений в различных компонентах приложения может привести к путанице.

На самом деле SQL Server определяет, какие из столбцов таблицы могут содержать неопределенные значения, с помощью столбца с битовой маской в системной таблице sysobjects. Очевидно, это приводит к дополнительным накладным расходам. Каждая агрегатная функция должна учитывать тот факт, что столбец может иметь неопределенное значение, и принимать специальные меры, чтобы неопределенные значения в столбце не исказили результаты. В общем, NULL-значения - это противные маленькие чудовища, которые нуждаются в специальной обработке.

Скажем справедливости ради, что неопределенные значения - это необходимое зло во многих случаях. Точные вычисления быстро становятся чрезмерно сложными, если нет прямой поддержки неопределенных значений.

Разница между нулевым и неизвестным значениями такая же, как между неким другим известным значением и неизвестным, - между ними концептуальная пропасть. Это все равно что иметь нулевой баланс счета и не иметь счета вообще. Столбцы типа datetime также часто должны позволять NULL-значения, потому что даты часто выражаются относительными, а не абсолютными величинами.

Один приемлемый метод для избежания использования NULL-значений - применение фиктивных значений для обозначения отсутствующих данных. Например, вместо NULL в символьных столбцах можно применить строки "N/A" или "NV". Можно использовать -1 для указания на отсутствующее значение во многих столбцах типа integer, "1900-01-01" можно задействовать для дат, и так далее. В этих случаях удобно применять функцию NULLIF(), особенно при работе с агрегатными функциями. Допустим, чтобы SUM() проигнорировала числовые столбцы, содержащие -1, вы можете выбрать что-то вроде SELECT SUM(NULLIF(c1, -1)), потому что SUM() игнорирует NULL-значения. Вы можете создать подобные выражения и для обработки фиктивных значений других типов.

Мораль этой истории такова: NULL - это как криптонит для Супермена - он высасывает жизнь из всего, что оказывается рядом. Используйте NULL-значения, если вам это необходимо, но по возможности избегайте их.

Cлово "криптонит" впервые появилось в одной из радиосерий о Супермене в 1943 году. Супермен практически неуязвим, ему не страшен град пуль, но перед криптонитовой радиацией он бессилен. - Примеч. перев.