"IS NULL" and "=NULL" - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

"IS NULL" and "=NULL"

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-21 : 11:24:37

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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 11:28:26

" = null" is not correct syntax in MS SQL Server

Corey

kroky
Starting Member

14 Posts

Posted - 2004-06-21 : 11:31:14

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 11:40:02

Hmm... oops.

Well that should solve the original question too then.

I think ill leave the settings alone for now

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-21 : 13:28:08

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-)

Kristen
Test

22859 Posts

Posted - 2004-06-21 : 13:49:15

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

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-21 : 13:51:19

Well there are a couple of options here

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

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 14:09:44

So wait... I wasn't wrong??? or I was?

Corey

Kristen
Test

22859 Posts

Posted - 2004-06-21 : 14:21:36

Did you SET your ANSI_NULL to ON or OFF? or MAYBE?!

Kristen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 14:40:26

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

Kristen
Test

22859 Posts

Posted - 2004-06-21 : 14:50:22

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 15:09:16

I could definitely agree with you there...

Corey

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-21 : 15:13:53

I don't know....I'd prefer not to mess with any setting....

Brett

8-)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-21 : 15:16:55

hear! hear!

Corey

chadmat
The Chadinator

1974 Posts

Posted - 2004-06-21 : 17:28:46

= 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources