NULL values are excluded. Why?

This is about a bizarre behaviour I found in Microsoft Sql Server. Please correct me if I'm wrong.

SELECT COUNT(*) FROM TABLEA 
WHERE [Column1] IS NULL;

This returns 30018 rows.

CREATE VIEW VIEWB AS 
SELECT * FROM TABLEA AS t1 
WHERE t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')  

If I check VIEWB, I don't find NULL in Column1:

SELECT COUNT(*) FROM VIEWB 
WHERE [Column1] IS NULL;

This returns 0 rows.

Why? The query above excludes the 3 values, but it isn't supposed to exclude NULL. Why does Ms Sql Server behave this way? Should I have expected this? How can I fix it?

Answers


This is actually a common mistake made with SQL Server in treating NULL as a value. By default, it's treated as UNKNOWN, as documented here. So, in your view, you also need to include an OR t1.[Column1] IS NULL.

You can change this behavior by calling SET ANSI_NULLS OFF. It is not recommended to use this, however, as the feature is deprecated as pointed out by @Martin Smith.

This is not a SQL Server specific issue, however. It's part of the ANSI SQL standard.


SQL uses three valued logic.

t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal') 

is equivalent to

t1.[Column1] <> 'Cross/Up sell' AND  
t1.[Column1] <> 'Renegotiation' AND 
t1.[Column1] <>  'Renewal')

When t1.[Column1] is NULL this expression evaluates to UNKNOWN rather than TRUE so these rows are not returned.

The only time NULL NOT IN ( ... ) will be returned is if the NOT IN clause evaluates to an empty set.


Best answer would be just to use below condition in where clause

ISNULL(t1.[Column1],'') NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')


Another example of taking care when dealing with nulls

I'm just agreeing with Sumo - why not just change the view to:

CREATE VIEW VIEWB AS 
SELECT * FROM TABLEA AS t1 
WHERE 
    t1.[Column1] NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')  
    OR
    t1 IS NULL 

an alternative might be the following which I've added to 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

Confirmed this is the best working solution for SQL Server 2016

WHERE ISNULL(t1.[Column1],'') NOT IN ('Cross/Up sell', 'Renegotiation', 'Renewal')


Need Your Help

Android CheckBox to show multiple selection of text in one string

android android-checkbox

I have 7 checkboxes which shows week days,Now what i want to do is to store the text of each check box in single string on basis of selected checkbox.

How can I insert values from SELECT SUM into other table?

mysql sql sql-server filemaker

I'm stuck and I could use a little input. How can I insert the SUM(amount) of all values of "accountKonto" of table "Buchung" into one row of the table "Erfolg"?