Bizarre WHERE col = NULL behavior

Question

This is a problem that one of our developers brought to me. He stumbled upon an old stored procedure that 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 if the WHERE col = NULL references are not changed to WHERE col IS NULL.

Can anyone explain this behavior?

Answer 1

What about the design: if you compare anything with null, it is rated as unknown. Any logic with unknownitself unknown. Therefore, any statement with anything = nullwill always be false.

An important distinction 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, the unknownwhole expression is disgusting.

Based on the comments, the best answer would probably be to check ANSI_NULL with:

SELECT SESSIONPROPERTY ('ANSI_NULLS')

If this returns false, the construct = nullwill work as 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

It is used by default ansi_nulls on, and it is very unusual to see that it is disabled. The stored procedure remembers the setting from the moment 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 following the procedure from SSMS.

Answer 2

, , :

ANSI_NULLS

Answer 3

In SQL, X = NULLit will always be evaluated as false, since NULL represents the lack of data, it is impossible to determine whether it is equal to a "different" lack of data or not ( NULL = NULL- false). That is why there is a keyword IS...