SQL Server – Why Is Null Ignored in Query Results

nullsql serversql-server-2008-r2

I have a table with similar structure to the below, and a field has a NULL value in it. When I query the table this record is not returned, it is omitted. How should the query be altered so that this record is returned?

Declare @Fiesty Table
(  
  numberfour varchar(100)
  ,valid varchar(20)
)

Insert Into @Fiesty (numberfour, valid) Values
('1ACRELBS', NULL), ('4ACRELBS', 'Green'), ('17ACRELBS', 'White')

Select * FROM @Fiesty WHERE valid NOT IN ('Green', 'Blue', 'White')

My expected returned result would be where numberfour = 1ACRELBS

Best Answer

SQL uses ternary (three-valued) logic and comparisons with NULL are tricky, counter intuitive. Regarding the condition:

WHERE valid NOT IN ('Green', 'Blue', 'White')

it expands to:

WHERE valid <> 'Green' AND valid <> 'Blue' AND valid <> 'White'

When valid is NULL, all these 3 sub-conditions evaluate to UNKNOWN (neither TRUE nor FALSE). The whole condition evaluates to UNKNOWN as well and thus the row rejected (WHERE keeps only the rows that the condition evaluates to TRUE).

When comparing nulls, you can think this way. I don't know the exact value of valid so I can't be sure if it's going to be different to 'Green' or not. It might be and it might be not. The result is UNKNOWN.

If the intention is to get in the result the rows where valid IS NULL, you can use either:

SELECT * FROM @Fiesty 
WHERE valid IS NULL
   OR valid NOT IN ('Green', 'Blue', 'White') ;

or EXISTS / EXCEPT (or NOT EXISTS / INTERSECT):

SELECT f.* FROM @Fiesty AS f 
WHERE EXISTS
      ( SELECT f.valid 
      EXCEPT 
        SELECT v.valid 
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
      ) ;

There is also a NOT EXISTS way which is my opinion to be preferred to NOT IN, especially because the behaviour regarding nulls is more intuitive. (Notice also the similarity with the INTERSECT version):

SELECT f.* FROM Fiesty AS f
WHERE NOT EXISTS
      ( SELECT *
        FROM
            ( VALUES ('Green'), ('Blue'), ('White') )
            AS v (valid)
        WHERE v.valid = f.valid
      ) ;

Test at dbfiddle.uk.

Related Question