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
Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49822 Visits: 803
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp



sunshinekid
sunshinekid
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 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.



Antares686
Antares686
SSC-Forever
SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)SSC-Forever (49K reputation)

Group: Moderators
Points: 49822 Visits: 803
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
Simon Sabin
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3364 Visits: 110
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
don1941
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1723 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.



sunshinekid
sunshinekid
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 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



alexd1980
alexd1980
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 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)
?



sunshinekid
sunshinekid
SSC Veteran
SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)SSC Veteran (202 reputation)

Group: General Forum Members
Points: 202 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.



JayTKay
JayTKay
SSC-Enthusiastic
SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)SSC-Enthusiastic (107 reputation)

Group: General Forum Members
Points: 107 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,'') = ''



zhudawei
zhudawei
SSC-Enthusiastic
SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)SSC-Enthusiastic (184 reputation)

Group: General Forum Members
Points: 184 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



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