Значения 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')