Understanding the difference between IS NULL and = NULL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by James Travis » Understanding the difference between IS NULL...

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Understanding the difference between IS NULL and = NULL

Rate Topic

Display Mode

Topic Options

Author

Message

Antares686

Posted Wednesday, December 11, 2002 12:00 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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

Post #8731

sunshinekid

Posted Thursday, December 12, 2002 3:32 AM

Grasshopper

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

Antares686

Posted Thursday, December 12, 2002 4:23 AM

SSCrazy Eights

Group: Moderators
Last Login: Tuesday, April 09, 2013 12:53 PM
Points: 8,357, Visits: 684

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

Simon Sabin

Posted Thursday, December 12, 2002 5:50 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, February 20, 2013 3:18 AM
Points: 573, Visits: 95

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

don1941

Posted Thursday, December 12, 2002 7:54 AM

SSC 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

sunshinekid

Posted Friday, December 13, 2002 4:16 AM

Grasshopper

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

alexd1980

Posted Sunday, December 15, 2002 7:45 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, December 09, 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

sunshinekid

Posted Monday, December 16, 2002 2:18 AM

Grasshopper

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

JayTKay

Posted Friday, June 20, 2003 9:11 PM

Grasshopper

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

zhudawei

Posted Thursday, August 19, 2004 8:17 PM

SSC 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 »

41 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions