Compare to NULL

[EriRobert]

Hi

I keep getting tripped up when comparing a column to Null because instead of saying

P_FieldName Is Null

I'm saying

P_FieldName = Null

This second condition doesn't fire if the field contains null. As a matter of interest - why?

 

[SQLDenis]

because NULL is unknown
take a look at this

declare @1 int,@2 int

select @1,@2 --both NULL
if @1 =@2 --not equal, since NULL is unknown nothing can be equal to NULL
print 'yes'
else
print 'no'

so you can not test with = but have to use Is NULL instead

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Mostly a matter of syntax. NULL is special singular value that does not obey same rules as "regular" values - so it requires special comparison operator (IS).

= NULL does not bomb out with runtime error... because sometimes you use = FieldName, and FieldName value may be NULL. Instead, such expressions always evaluate to false by default.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[EriRobert]

mmm...

would be nice if syntax checker 'warned' (I know there is no such thing) you against a statement which is nonsense:

Field = Null

Or is there an instance where such a statement would be meaningful (apart from fifth-column coding!)