CODEWITHSUNDEEP
sqlsql-servert-sql
Philip
Philip

Reputation: 2628

NOT condition excluding NULL values

I have the below sample data.

This query returns the data I need to:

select * from #temp
where (Field1 = 'Test1' or Field1 = 'Test10') and
        Field2 = 'Test2' and  
        (Field3 = 'Test3' or Field3 = 'Test30')

However, I need another query that basically shows the records that don't meet the above conditions (an exception query).

If I use the following query, it doesn't show the NULL values. Why is that? How can I get the row with the NULL value to show?

select * from #temp
where NOT (Field1 = 'Test1' or Field1 = 'Test10') and NOT
        Field2 = 'Test2' and   NOT
        (Field3 = 'Test3' or Field3 = 'Test30')

Create Table #temp
(
    ID int,
    Field1 varchar(50),
    Field2 varchar(50),
    Field3 varchar(50)
)

Insert into #temp
(
    ID,
    Field1,
    Field2,
    Field3
)
select
    1,
    'Test1',
    'Test2',
    'Test3'
union all
select
    2,
    NULL,
    NULL,
    NULL
union all
select
    3,
    'Test',
    'Test',
    'Test'

Upvotes: 3

Views: 1871

Answers (2)

Abel
Abel

Reputation: 86

NULLs are not equal to any value or different than any value. They are unknown.

To deal with nulls you can use IS NULL and IS NOT NULL.

SELECT * FROM #temp
WHERE (
Field1 NOT IN ('Test1', 'Test10')
    OR Field1 IS NULL) 
AND (Field2 <> 'Test2' OR Field2 IS NULL)
AND (Field3 NOT IN ('Test3', 'Test30')
    OR Field3 IS NULL)

As an extra tip, you can change 

Field1 = 'Test1' or Field1 = 'Test10'

For

Field1 IN ('Test1', 'Test10')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Almost any comparison to NULL returns NULL, which is treated as false.

One rather expensive method is to use EXCEPT:

select t.*
from #temp t
except
select t.*
from #temp t
where (Field1 = 'Test1' or Field1 = 'Test10') and
      Field2 = 'Test2' and  
      (Field3 = 'Test3' or Field3 = 'Test30');

Note that this eliminates duplicate rows.

I would simplify the clause to:

where Field1 in ('Test1', 'Test10') and
      Field2 = 'Test2' and  
      Field3 in ('Test3', 'Test30');

Obviously, you can reconstruct the where clause to take null values into account. That is really cumbersome and prone to error. So, an alternative is to create a flag and just use the flag:

select t.*
from #temp t cross apply
     (values (case when (Field1 = 'Test1' or Field1 = 'Test10') and
                        Field2 = 'Test2' and  
                        (Field3 = 'Test3' or Field3 = 'Test30')
                   then 1 else 0
              end)
      ) v(flag)

Then:

 where v.flag = 1   -- for inclusion
 where v.flag = 0   -- for exclusion

Upvotes: 3

Related Questions