Bizarre WHERE col = NULL behavior

Question

This is a problem one of our developers brought to me. He stumbled across an old stored procedure which used 'WHERE col = NULL' several times. When the stored procedure is executed it returns data.

If the query inside the stored procedure is executed manually it will not return data unless the 'WHERE col = NULL' references are changed to 'WHERE col IS NULL'.

Can anyone explain this behavior?

Answer 1

That's by design: if you compare anything to null, it evaluates to unknown. Any logic with unknown is itself unknown. So any statement with anything = null will always be false.

The important difference is between these two constructs:

1 = null --> unknown
1 is null --> false

So:

1 = null or 1=1 --> unknown (false)
1 is null or 1=1 --> true

So as you can see, unknown taints an entire expression.

Based on the comments, a better answer would probably be checking for ANSI_NULLs, with:

SELECT SESSIONPROPERTY ('ANSI_NULLS')

If this returns false, the = null construct would work like is null:

set ansi_nulls on -- default
SELECT SESSIONPROPERTY ('ANSI_NULLS') -- 1
select 1 where not null = 1 -- no rows returned
set ansi_nulls off
SELECT SESSIONPROPERTY ('ANSI_NULLS') -- 0
select 1 where not null = 1 -- returns a row

The default is ansi_nulls on though, and it's very unusual to see it turned off. A stored procedure does remember the setting from the time it was created:

set ansi_nulls off
go
create procedure dbo.TestNulls as select 1 where not null = 1
go
set ansi_nulls on
exec dbo.TestNulls -- Still prints a row

You can check the saved settings by scripting the procedure from SSMS.

Answer 2

In SQL, X = NULL will always evaluate to false, since NULL represents the absence of data, one cannot tell whether it is equal to "another" absence of data or not (NULL = NULL is false). This is why the IS keyword exists...

Answer 3

OK, guess I should have made this an answer:

Check the ANSI_NULLS setting