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 Wednesday, December 11, 2002 12:00 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 8:57 AM
Points: 8,369, Visits: 736
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp


Post #8731
Posted Thursday, December 12, 2002 3:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 11, 2007 7:20 AM
Points: 12, Visits: 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.





Post #49258
Posted Thursday, December 12, 2002 4:23 AM
SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: Moderators
Last Login: Yesterday @ 8:57 AM
Points: 8,369, Visits: 736
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.




Post #49259
Posted Thursday, December 12, 2002 5:50 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 13, 2014 10:40 AM
Points: 573, Visits: 107
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
Post #49260
Posted Thursday, December 12, 2002 7:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, October 17, 2003 12:00 AM
Points: 299, Visits: 1
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.




Post #49261
Posted Friday, December 13, 2002 4:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 11, 2007 7:20 AM
Points: 12, Visits: 1
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





Post #49262
Posted Sunday, December 15, 2002 7:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 9, 2003 12:00 AM
Points: 2, Visits: 1
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)
?




Post #49263
Posted Monday, December 16, 2002 2:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 11, 2007 7:20 AM
Points: 12, Visits: 1
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.




Post #49264
Posted Friday, June 20, 2003 9:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 10:34 AM
Points: 23, Visits: 22
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,'') = ''




Post #49265
Posted Thursday, August 19, 2004 8:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 16, 2007 1:05 AM
Points: 86, Visits: 3
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


Post #133007
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse