Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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"
 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
22858 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
22858 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
22858 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
1974 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  
 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.05 seconds. Powered By: Snitz Forums 2000