Значения NULL исключены. Зачем?
Это о странном поведении, которое я обнаружил в Microsoft Sql Server. Пожалуйста, поправьте меня, если я ошибаюсь.
SELECT COUNT(*) FROM TABLEA
WHERE [Column1] IS NULL;
Это возвращает 30018 строк.
CREATE VIEW VIEWB AS
SELECT * FROM TABLEA AS t1
WHERE t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')
Если я проверяю VIEWB
, я не нахожу NULL
в Column1
:
SELECT COUNT(*) FROM VIEWB
WHERE [Column1] IS NULL;
Это возвращает 0 строк.
Почему? В приведенном выше запросе исключаются 3 значения, но он не должен исключать NULL. Почему Ms Sql Server ведет себя так? Должен ли я ожидать этого?
Как я могу это исправить?
Ответы
Ответ 1
Это обычная ошибка, допущенная SQL Server при обработке NULL в качестве значения. По умолчанию он рассматривается как UNKNOWN, как описано здесь. Итак, на ваш взгляд, вам также необходимо включить OR t1.[Column1] IS NULL
.
Вы можете изменить это поведение, вызвав SET ANSI_NULLS OFF
. Однако не рекомендуется использовать это, поскольку функция устарела, как указано @Martin Smith.
Однако это не проблема SQL Server. Это часть стандарта ANSI SQL.
Ответ 2
SQL использует трехзначную логику.
t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')
эквивалентно
t1.[Column1] <> 'Cross/Up sell' AND
t1.[Column1] <> 'Renegotiation' AND
t1.[Column1] <> 'Renewal')
Когда t1.[Column1] is NULL
это выражение оценивается как UNKNOWN
, а не TRUE
, поэтому эти строки не возвращаются.
Единственное время NULL NOT IN ( ... )
будет возвращено, если предложение NOT IN
оценивается в пустом наборе.
Ответ 3
Лучший ответ заключался бы в том, чтобы использовать ниже условие в where where
ISNULL (t1. [Column1], '') NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')
Ответ 4
Еще один пример заботы при работе с nulls
Я просто соглашаюсь с Сумо - почему бы просто не изменить представление на:
CREATE VIEW VIEWB AS
SELECT * FROM TABLEA AS t1
WHERE
t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')
OR
t1 IS NULL
альтернативой может быть следующее, которое я добавил в SQL FIDDLE
CREATE VIEW VIEWB AS
SELECT * FROM TABLEA AS t1
WHERE
1 = CASE
WHEN ISNULL(t1.[Column1],'x') NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal') THEN 1
ELSE 0
END
Ответ 5
Подтверждено, что это лучшее рабочее решение для SQL Server 2016
ГДЕ ISNULL (t1. [Column1], '') NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')