Lynn Pettis (3/23/2009)

---

kevin.l.williams (3/23/2009)

---

I know why the null value is not returned but I am puzzled by the explanation.

"Since col2 is used in the WHERE clause, the output does not include the corresponding col1 value i.e. 4, even though no such 'not equal to NULL' condition is specified."

It works like this, the records returned by the following query:

select col1 from @test-2 where col2 not like '%del%'

are those where the value in col2 is not like '%del%', meaning the the NOT LIKE is true. However, is col2 is null, this result of the NOT LIKE is false.

Try the following:

declare @TestVal int;

set @TestVal = null; -- ensure @TestVal is null

select

case when @TestVal = null then 'equal null'

when @TestVal <> null then 'not equal null'

else 'undetermined'

end;