SQL-Ex blog

Решение

В большинстве случаев ваши столбцы, вероятно, допускают NULL-значения, если вы сознательно не изменили их, когда создавали таблицу, или же изменили установки по умолчанию для вашей базы данных при помощи опций SET ANSI_NULL_DEFAULT OFF и SET ANSI_NULL_DFLT_ON OFF. Эти установки меняют поведение при создании таблицы, если вы при этом не указываете NULL или NOT NULL. Когда создается новая база данных, она будет использовать настройки базы данных model, чтобы определить установку ANSI_NULL_DEFAULT, если она не будет указана при создании базы данных.

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

Пример 1

Когда эти две опции выключены (OFF), по умолчанию NULL не допускаются.

CREATE DATABASE TestNulls

GO

USE TestNulls

GO

-- изменяет установку для базы данных на off

ALTER DATABASE TestNulls SET ANSI_NULL_DEFAULT OFF;

GO

SET ANSI_NULL_DFLT_ON OFF;

GO

CREATE TABLE Table1 (a TINYINT);

GO

sp_help Table1

GO

На скриншоте показано, что столбец не допускает NULL-значений.

Пример 2

Когда эти две опции включены (ON), значением по умолчанию является допустимость NULL-значений.

USE TestNulls

GO

ALTER DATABASE TestNulls SET ANSI_NULL_DEFAULT ON;

GO

SET ANSI_NULL_DFLT_ON ON;

GO

CREATE TABLE Table2 (a TINYINT);

GO

sp_help Table2

GO

На скриншоте видно, что столбец допускает NULL-значения.



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

Установка теста

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

USE TestNulls

GO

CREATE TABLE [dbo].[CarModels](

  [Make] [varchar](50) NULL,

  [Model] [varchar](50) NULL,

  [Trim] [varchar](50) NULL

) ON [PRIMARY]

GO 

CREATE TABLE [dbo].[Orders](

  [Make] [varchar](50) NULL,

  [Model] [varchar](50) NULL,

  [Trim] [varchar](50) NULL

) ON [PRIMARY] 

GO

INSERT INTO dbo.Orders VALUES ('Nissan','Altima','2-door 2.5 S Coupe')

INSERT INTO dbo.Orders VALUES ('Nissan','Altima','4-door 3.5 SE Sedan')

INSERT INTO dbo.Orders VALUES ('Nissan','Altima','')

INSERT INTO dbo.Orders VALUES ('Nissan','Altima',NULL) 

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','2-door 2.5 S Coupe')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','2-door 3.5 SE Coupe')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door 2.5 S Sedan')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door 3.5 SE Sedan')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door 3.5 SL Sedan')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima','4-door HYBRID Sedan')

INSERT INTO dbo.CarModels VALUES ('Nissan','Altima',NULL)

Выборка данных

Покажем сначала данные в этих двух таблицах. Идея в том, что мы хотим соединить таблицы по Make, Model и Trim. Для большиства записей там имеются значения, но есть несколько записей, для которых значением Trim является NULL.

SELECT * FROM dbo.Orders a 

SELECT * FROM dbo.CarModels b

Первый запрос выполняет прямое соединение этих таблиц по всем трем столбцам.

SELECT * 

FROM dbo.Orders a

INNER JOIN dbo.CarModels b 

   ON a.Make = b.Make 

   AND a.Model = b.Model 

   AND a.Trim = b.Trim

Запросу выше отвечают только три из четырех записей таблицы Orders. Записи, которые содержат NULL в Trim, не попадают в результирующий набор.



Следующий пример использует функцию ISNULL в предложении join. Функция ISNULL принимает два параметра, первый - это значение, которое проверяется на NULL, и если оно равно NULL, то второй параметр указывает, во что должно быть преобразовано это значение. Итак, в этом примере значение NULL будет преобразовано в пустую строку ''.

SELECT * 

FROM dbo.Orders a

INNER JOIN dbo.CarModels b 

   ON a.Make = b.Make 

   AND a.Model = b.Model 

   AND isnull(a.Trim,'') = isnull(b.Trim,'')

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



Чтобы сделать еще один шаг вперед, снова используем функцию ISNULL, но теперь преобразуем NULL в '999999'. Это значение, которого, как мы знаем, нет в нашей таблице и, следовательно, оно не будет вызывать нежелательных соединений.

SELECT * 

FROM dbo.Orders a

INNER JOIN dbo.CarModels b

   ON a.Make = b.Make 

   AND a.Model = b.Model 

   AND isnull(a.Trim,'999999') = isnull(b.Trim,'999999')

Вот окончательный результат с четырьмя строками, которые мы ожидали получить.

Другие варианты

После первоначального написания этой статьи в комментариях было предложено несколько альтернативных решений.

Вариант 1

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

Вариант 2

Вот еще один вариант запроса, который возвращает тот же окончательный результат.

SELECT *

FROM dbo.Orders AS O

JOIN dbo.CarModels AS CM

  ON CM.Make = O.Make 

  AND CM.Model = O.Model 

  AND EXISTS (

              SELECT O.Trim

              INTERSECT

              SELECT CM.Trim

              );

Вариант 3

Вот другой вариант запроса, который возвращает тот же результат.

SELECT * 

FROM dbo.Orders a

INNER JOIN dbo.CarModels b

   ON a.Make = b.Make 

   AND a.Model = b.Model 

   AND ( a.Trim = b.Trim OR ( a.Trim IS NULL AND b.Trim IS NULL ) )

Потенциальные проблемы производительности

Хотя функция ISNULL удобна, но если вы делаете большие соединения с этой функцией в предложении join, это замедлит запрос. Многое также будет зависеть от индексов, которые вы построили, и как индексы используются в соединении.

В общем это не хорошая идея использовать функции в соединениях или в левой части предложения WHERE, поскольку SQL Server должен проверять каждое значение и, следовательно, может отказаться от использования индекса. Поскольку в некоторых случаях нет других вариантов, поэтому вам приходится это делать. Но вы должны знать об этой потенциальной проблеме производительности.

Заключение

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

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