SQL And NULL Values in where clause

Question

So I have a simple query that returns a listing of products

SELECT     Model, CategoryID
FROM         Products
WHERE     (Model = '010-00749-01') 

This returns

010-00749-01    00000000-0000-0000-0000-000000000000
010-00749-01    NULL

Which is correct, so I wanted only the products whose CategoryID is not '00000000-0000-0000-0000-000000000000' so I have

SELECT     Model, CategoryID
FROM         Products
WHERE     (Model = '010-00749-01') 
AND (CategoryID <> '00000000-0000-0000-0000-000000000000') 

But this returns no result. So I changed the query to

SELECT     Model, CategoryID
FROM         Products
WHERE     (Model = '010-00749-01') 
AND ((CategoryID <> '00000000-0000-0000-0000-000000000000') OR  (CategoryID  IS NULL))

Which returns expected result

010-00749-01    NULL

Can someone explain this behavior to me? MS SQL Server 2008

Answer 1

Basically, a NULL is the absence of any value. So trying to compare the NULL in CategoryId to a varchar value in the query will always result in a false evaluation.

You might want to try using the COALESCE function, something like:

SELECT     ModelId, CategoryID 
FROM       Products 
WHERE      (ModelId = '010-00749-01')  
AND        ( COALESCE( CategoryID, '' ) <> '00000000-0000-0000-0000-000000000000' ) 

EDIT

As noted by AdaTheDev the COALESCE function will negate any indices that may exist on the CategoryID column, which can affect the query plan and performance.