SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding the difference between IS NULL and = NULL


Understanding the difference between IS NULL and = NULL

Author
Message
David le Quesne
David le Quesne
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1063 Visits: 32

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
Kelvin Lush
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 91

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
Chris Stamey
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 29

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
Kelvin Lush
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 91

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
Antares686
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: Moderators
Points: 12110 Visits: 780
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.



Chris Stamey
Chris Stamey
SSC-Enthusiastic
SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)SSC-Enthusiastic (175 reputation)

Group: General Forum Members
Points: 175 Visits: 29

I just ran into this yesterday, and could not get a count for Null values if I was using the field with the Nulls in it for the count(), regardless of the ANSI Nulls setting. If I used a different field, such as the ID field, in the count(), I did get the count correctly.

The query:
Select Count(PremiumGroup), PremiumGroup From RetireePremiums
Group by PremiumGroup
Order by PremiumGroup

The results:
0 NULL
27 A
94 B
124 C
32 D
345 E
193 F
16 G
195 New LA
7 Special

There are actually over 2000 records with a Null in that field.

But in any case, Null can be undetermined value, value not assigned, absence of value, etc. but in reality it is the same thing, no value, and should be recognized as its own special value to make logic easier when programming.

I am unable to imagine how making Nulls easier to deal with could make life more difficult for any of us, or our logic.

Thanks,
Chris


Kelvin Lush
Kelvin Lush
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1242 Visits: 91
Thanks Antares. I never realised that there was so much confusion about NULL's. You have clearly highlighted a big problem area.



Victor Virrueta
Victor Virrueta
SSC Veteran
SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)SSC Veteran (232 reputation)

Group: General Forum Members
Points: 232 Visits: 1

hi!!, i was playing around with my QA and tryin' to find the perfect query to avoid this NULL issues. Of course, i still haven't found it.

I think this is like... try to explain what means nothing (in Real Life ).

SET ANSI_NULLS [Off | on]

DECLARE @val1 int
DECLARE @val2 int

If (isnull(@val1,null) = isnull(@val2,null))
print 'True'
else
print 'False'

SET @val1 = NULL
SET @val2 = NULL

If (isnull(@val1,null) = isnull(@val2,null))
print 'True'
else
print 'False'


Well i know this could not happen in real cases, but is curious how the result of this is always False, why??, i'm not expert in SQL Server but.. for SET ANSI_NULLS Off .. the result shouldn't be TRUE???

can anybody explain this??..

thanks!!


sara karasik
sara karasik
SSC-Enthusiastic
SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)SSC-Enthusiastic (191 reputation)

Group: General Forum Members
Points: 191 Visits: 95

Familiarize yourself with the SQL function isnull().

When comparing a field which may have a null value, instead of = CustomerID

use = isnull(CustomerID, '')

which means: If the customerid is null, treat it as '', an empty string.

Good Luck


Kenneth Lee
Kenneth Lee
SSC-Enthusiastic
SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)SSC-Enthusiastic (139 reputation)

Group: General Forum Members
Points: 139 Visits: 1

FYI sql like 'customerid = ' & sCustomerID

Can be 'isnull(customerid) = isnull(' & sCustomerID & ')'

or it can be '(customerid is null and ' & sCustomerID & ' is null) or customerid = ' & sCustomerID


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search