Understanding the difference between IS NULL and = NULL

Question

Understanding the difference between IS NULL and = NULL

Antares686

SSC Guru

Points: 125444
More actions
December 11, 2002 at 12:00 am

#159306

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

Viewing 15 posts - 1 through 15 (of 40 total)

You must be logged in to reply to this topic. Login to reply

sunshinekid Old Hand Points: 324 More actions · Answer 1

I realised the topic specifically covered variables, but some of these aspects also apply more widely to any expression involving null.

Under sql92 any expression involving null directly evaluates to unknown, rather than true/false, hence the issues with relational operators.

Operators like IS NULL explicitly convert back to 2-valued logic so you can do some sensible boolean algebra.

Note that different versions of t-sql have used different implementations of = (and possibly other operators) when used with NULL.

In early versions of sqlserver (thosed based on Sybase, up to 6.0 I think), selecting values from a table using = NULL used the ANSI meaning and never matched any records. This has since changed and with ansi nulls off you will get 'matching' records.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

Hey Sunshine

I looked all over and did find all the details I wanted, so I appreciate that additional information. I wrote the article as I keep seeing this issue show up in questions on the forums. I based my information of course on a basic understanding of C++ and the way the variables react in the situations I could directly affect. However, the information you add here, can you point me to where you found this.

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 3

The other aspect to be aware is that NULL + 'hello world' = NULL in SQL 7 on wards without ansi nulls off

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

don1941 SSCarpal Tunnel Points: 4183 More actions · Answer 4

This was my first hard lesson learned in SQL. I've never forgotten it these past 8-9 years. I think every programming standards guide ought to mention this (as a running footer on every printed page!) because it's the #1 question i still get on every project.

sunshinekid Old Hand Points: 324 More actions · Answer 5

Anyone interested in digging further should check out BOL.

Its simplest to look for 'NULL Comparison Search Conditions' in the index & follow the link to 'Null Values' at the bottom of the article (its part of the expression syntax).

Null behaviour is documented quite well in these sections, although previously my info has mostly been gleaned from empirical research & helping those with broken code.

This area is a classic example of where changing a server default can break your code quite horribly:

BP - always use 'set ansi nulls on' for each session

WP1 - forget to check the server setting

WP2 - assume behaviour is the same across versions

alexd1980 Valued Member Points: 72 More actions · Answer 6

Strange:

I issue this

DECLARE @val CHAR(4)

If @val = NULL

select 'Yup1'

else

select 'Nop1'

SET @val = NULL

If @val = NULL

select 'Yup2'

else

select 'Nop2'

there is a result:

----

Nop1

(1 row(s) affected)

----

Nop2

(1 row(s) affected)

?

sunshinekid Old Hand Points: 324 More actions · Answer 7

alexd1980, you probably have ansi nulls turned on.

In this case NULL=NULL always evaluates to unknown, so your if expression evaluates the ELSE branch.

This is why it doesn't matter what the value of your variable is.

JayTKay SSC Veteran Points: 277 More actions · Answer 8

Too lazy to remember the rules about nulls and ANSI NULLS and empty strings, so I avoid tripping over them.

Typically, I use comparisons like this:

isnull(@variable,'') = ''

isnull(@variable,0) = 0

coalesce(@variable,@anothervariable,'') = ''

zhudawei Old Hand Points: 310 More actions · Answer 9

It is very hard to remember the rules. Of course we can always use 'is null' in sql however the problem is in asp code sometime you have to make up the sql like 'customerid = ' & sCustomerID. In this case, it's also too much work to change '=' to 'is' when sCustomerID is NULL

David le Quesne SSCertifiable Points: 5229 More actions · Answer 10

The Borland Delphi application I administer uses a SQL Server 2000 back end with ANSI NULLS set to ON. If I remove a date from a field in the application, it doesnt return to NULL, it returns to a non-NULL value which is not displayed, so I have to explicitly set the value to NULL if the user removes the date otherwise my IS NULL comparisons will not work

David le Quesne

If it ain't broke, don't fix it...

Kelvin Lush SSCrazy Points: 2602 More actions · Answer 11

I agree with axeld1980.

The statements made about setting a value to null rather than just not setting it are incorrect. It makes absolutely no difference whether the value is assigned to null or not.

The results you get are wholly dependent on the value for ANSI_NULLS. With this set to ON, nothing will ever '= NULL', not even if it is set explicitly. With the setting OFF, both '= NULL' and 'IS NULL' behave in the same way. So:

SET ANSI_NULLS [ON|OFF]

DECLARE @val CHAR(4)

If @val = NULL

print 'True'

else

print 'False'

SET @val = NULL

If @val = NULL

print 'True'

else

print 'False'

When 'ON', both tests return 'False'. When 'OFF', both tests return 'True'.

This is the case for both SQL 7 and 2000. Try it for yourselves!

Chris Stamey SSCrazy Points: 2695 More actions · Answer 12

WHY CAN'T NULL = NULL?????????????

That has always been my biggest problem in dealing with Nulls. Null does equal Null in the real world. If Null = the absence of a value, and you have two instances of the absence of a value, they are the same, Null.

If the field value is Null, the absence of a value in that field, IT EQUALS NULL!!!

I just don't get why it has to be so complicated. Humans write the software and we can make it evaluate Nulls any way we want to, so why can't it just be simple, Null = Null, "Is Null" should be the same thing as "= Null", but not the same thing as "= 'Null'".

"Select Count(Field1) Where Field1 Is Null" should return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

I think we could all get along just fine knowing that Null is a special value (or lack of) that is different than 'Null' (string of letters) but Null = Null.

Chris

Kelvin Lush SSCrazy Points: 2602 More actions · Answer 13

WHY CAN'T NULL = NULL?????????????

Because NULL in SQL 92 is taken to mean Unknown Value, not NO Value. If the value is unknown, how can you say that it equals anything?

That is why any test for '= NULL' will fail.

Also, re your comment about using IS NULL,

"Select Count(Field1) Where Field1 Is Null" DOES return the number of records that have a Null value for that field, regardless ofthe ANSI Nulls setting.

Antares686 SSC Guru Points: 125444 More actions · Answer 14

I see this. When I tested wrote and tested this I am fairly sure I used QA (not sure if was 7 or 2000 thou) and against multiple 7 instances but it would have been pre SP4 on SQL 7. If I get a chance I will probe into what changed the effect on SQL 7 and when. But at the time of the articles write up that was the effect you would get. Just one additional reason IS NULL is a better choice because if there was a fundamental chage in evaluation then your expected results may not be what you got after whatever changed this. It'll probably be a month before I can test thou as I am in the middle of a big project.