CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
cdsln
cdsln

Reputation: 888

Why is comparing NOT (x= 0) and NOT (x = NULL) returning the same values?

With the following data:

ItemID      PrivateSales            PublicSales             TotalSales                   SalesPerson
----------- ----------------------- ----------------------- ---------------------------- -----------------------------
1           300                     400                     700                          12
1           NULL                    10                      10                           12
2           NULL                    NULL                    NULL                         NULL
3           NULL                    NULL                    NULL                         NULL
6           20                      900                     920                          11
8           22                      18                      40                           10
9           20                      980                     1000                         11
22          NULL                    NULL                    NULL                         NULL
11          NULL                    NULL                    NULL                         NULL
15          40                      NULL                    40                           77
16          12                      NULL                    12                           NULL
44          NULL                    44                      44                           23

When I run the following two different queries, I get the exact same results:

 SELECT TOP 1000 [ItemID]
    ,[PrivateSales]
    ,[PublicSales]
    ,[TotalSales]
    ,[SalesPerson]
FROM [Sales]
WHERE NOT (
    PrivateSales IS NULL
    AND PublicSales IS NULL
    AND TotalSales IS NULL
    )

AND: 

SELECT TOP 1000 [ItemID]
    ,[PrivateSales]
    ,[PublicSales]
    ,[TotalSales]
    ,[SalesPerson]
FROM [Sales]
WHERE NOT (
    PrivateSales = 0
    AND PublicSales = 0
    AND TotalSales = 0
    )

Considering the NULL columns, why is the WHERE NOT (x = 0) not returning the NULL columns? The *Sales columns are decimal types if that matters.

Upvotes: 0

Views: 111

Answers (2)

Kryptonian
Kryptonian

Reputation: 870

See this link for nulls,

comparing any true or false value with null results in null , effectively you are to negating a null which does not yield true or false It is not returning null values as null is undetermined so it is not fulfilling condition that it is not equal to 0 or equal to 0 (it is unknown)

comparing NOT (x= 0) and NOT (x = NULL) should be 

 NOT (x= 0) And x is not null

or 

x<>0 and x is not null

reiterating lad2025's comment

"NULL with <>, !=, =, <, > is always NULL. Period. NOT (x=0) is the same as x <> 0"

Upvotes: 1

Pieter Geerkens
Pieter Geerkens

Reputation: 11893

The Law of Excluded Middle, that is to say that A or NOT A being a tautology, is lost with introduction of the third truth value NULL. SQL Server's implementation of this fact is to interpret a Boolean NULL as UNKNOWN; to propagate UNKNOWN; and to coalesce UNKNOWN to FALSE unless an explicit test against UNKNOWN, aka NULL, has been performed.

Upvotes: 1

Related Questions