Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Understanding the difference between IS NULL and = NULL Expand / Collapse
Author
Message
Posted Friday, June 3, 2005 12:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, 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...
Post #187311
Posted Friday, June 3, 2005 2:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:53 AM
Points: 1,211, Visits: 79

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!




Post #187332
Posted Friday, June 3, 2005 7:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2008 6:24 AM
Points: 115, 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

Post #187431
Posted Friday, June 3, 2005 7:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:53 AM
Points: 1,211, Visits: 79

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.




Post #187436
Posted Friday, June 3, 2005 7:57 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Friday, November 14, 2014 10:06 AM
Points: 8,370, Visits: 745
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.


Post #187451
Posted Friday, June 3, 2005 8:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 31, 2008 6:24 AM
Points: 115, 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

Post #187464
Posted Friday, June 3, 2005 8:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:53 AM
Points: 1,211, Visits: 79
Thanks Antares. I never realised that there was so much confusion about NULL's. You have clearly highlighted a big problem area.


Post #187465
Posted Friday, June 3, 2005 8:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, January 30, 2006 9:11 AM
Points: 230, 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!!

 

Post #187488
Posted Friday, June 3, 2005 9:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:57 AM
Points: 133, Visits: 89

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

 

Post #187513
Posted Friday, June 3, 2005 4:36 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, August 18, 2005 4:21 PM
Points: 75, 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

 

Post #187598
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse