Alex Rivera | Logout

SQL And NULL Values in where clause

Asked 2010-10-14T21:04:25.097
12

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

sql sql-server sql-server-2008 null
Edit
Report

3 Answers

2

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.

answered 2010-10-14T21:18:33.607
1

look at this:

1=1        --true
1=0        --false
null=null  --false
null=1     --false

1<>1       --false
1<>0       --true
null<>null --false
null<>1    --false    <<<--why you don't get the row with: AND (CategoryID <> '00000000-0000-0000-0000-000000000000')
answered 2010-10-14T21:14:22.430
0

Null gets special treatment. You need to explicitly test for null. See http://msdn.microsoft.com/en-us/library/ms188795.aspx

answered 2010-10-14T21:13:43.703

Your Answer