NULL values are excluded. Why?

user_1464026 0
Viewed 26987

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?

sql-server-2008 sql-server sql
5 Answers

There is another way when dealing with null values. We can use

coalesce(t1.[Column1], ' ') not in ('Cross/Up sell', 'Renegotiation', 'Renewal')
user_185597040
Related