SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 "IS NULL" and "=NULL"		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts
Posted - 06/21/2004 :  11:24:37  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Manik writes "Hi,
Please let me know why the following queries return two different result sets.
USE PUBS
SELECT * FROM discounts WHERE stor_id IS NULL
SELECT * FROM discounts WHERE stor_id = NULL

Thanks,
Manik"

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/21/2004 :  11:28:26  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
" = null" is not correct syntax in MS SQL Server



Corey
Go to Top of Page

kroky
Starting Member

14 Posts
Posted - 06/21/2004 :  11:31:14  Show Profile  Reply with Quote
YOu shouuld check you Ansi_Nulls setting

=null is supported in MS SQL Server.

this returns b

set ansi_nulls on
if null=null
print 'a'
else
print 'b'

and this returns a

set ansi_nulls off
if null=null
print 'a'
else
print 'b'

thats where the difference comes
null is null no matter of the ansi_null setting
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/21/2004 :  11:40:02  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Hmm... oops.

Well that should solve the original question too then.

I think ill leave the settings alone for now



Corey
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 06/21/2004 :  13:28:08  Show Profile  Reply with Quote
Null is not equal to anything inclusing itself...however a column can identify the non existance of a value, which would be IS NULL

and you can't say = Null in DB2



Brett

8-)
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 06/21/2004 :  13:49:15  Show Profile  Reply with Quote
I wish SQL would raise a syntax error, or somesuch, when I accidentally type "= NULL" ... takes me a while to find it the couple of times a year it happens :-) And as it happens it happened today and happened to take me a long time to find :-(

Kristen
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 06/21/2004 :  13:51:19  Show Profile  Reply with Quote
Well there are a couple of options here

http://weblogs.sqlteam.com/brettk/archive/2004/02/05/841.aspx



Brett

8-)
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/21/2004 :  14:09:44  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
So wait... I wasn't wrong??? or I was?


Corey
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 06/21/2004 :  14:21:36  Show Profile  Reply with Quote
Did you SET your ANSI_NULL to ON or OFF? or MAYBE?!

Kristen
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/21/2004 :  14:40:26  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Who me???

I have them set off (or at least whatever the default is...)

My original comment was that
quote:
" = null" is not correct syntax in MS SQL Server


So what I gather is: that is correct if Ansi_Null is Off; Incorrect if Ansi_Null is On.

I don't personally care b/c I always use '@a is null', I'm just trying to get my facts straight.


Corey
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 06/21/2004 :  14:50:22  Show Profile  Reply with Quote
I think that with SET ANSI_NULL OFF then the use of "= NULL" should raise a syntax error so that I don't do it by accident

Kristen
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/21/2004 :  15:09:16  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
I could definitely agree with you there...

Corey
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 06/21/2004 :  15:13:53  Show Profile  Reply with Quote
I don't know....I'd prefer not to mess with any setting....



Brett

8-)
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts
Posted - 06/21/2004 :  15:16:55  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
hear! hear!

Corey
Go to Top of Page

chadmat
The Chadinator

USA
1953 Posts
Posted - 06/21/2004 :  17:28:46  Show Profile  Visit chadmat's Homepage  Reply with Quote
= NULL is incorrect. However you can make it work by changing the setting (I don't recommend that).

IS NULL is proper syntax.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000