Select Query - 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

General SQL Server Forums

New to SQL Server Programming

Select Query

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-22 : 06:56:43

Hi,

I have the following table (dbo.table1) in sql server 2005..

A B C
1 54 No
1 78 No
2 26 NULL
2 98 NULL

So, I executed the following query;

select * from dbo.table1
where C <> 'Yes'

The result I expected was

A B C
1 54 No
1 78 No
2 26 NULL
2 98 NULL

However, I received the follwoing results

A B C
1 54 No
1 78 No

Why are fields with the NULL values excluded?

Note: C has a varchar(50) datatype..

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-22 : 07:04:54

NULL means the value is undefined, so you cannot compare them to anything other than NULL.

1) SELECT * FROM Table1 WHERE C IS NULL OR C <> 'Yes'
2) SELECT * FROM Table1 WHERE ISNULL(C, 'Yes') <> 'Yes'

Noone else can to this for you, because you are the only one who know that value NULL represent in your table.
Does it mean "I don't know if it's Yes or No", or does it simply mean "Not No"?

N 56°04'39.26"
E 12°55'05.63"

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-22 : 07:08:45

NULL means 'I dont know if it's Yes or No'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-22 : 07:09:22

When ANSI_NULL setting is set to ON, the ANSI SQL-92 standard states that any equal (=) or non equal (<>) statement must equate to FALSE when compared against a NULL.

You can use WHERE ISNULL(C,'') <> 'Yes' to get the desired result
or you can use Where (C <> 'Yes' OR C IS NULL)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-22 : 07:10:14

OMG I'm slow

No, you're never too old to Yak'n'Roll if you're too young to die.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-22 : 07:13:49

Recently, our server where SQL server 2005 is was patched could this in anyway changed the ANSI_NULL settings .... before I could see the NULL values but only recently we noticed we couldn't.

Secondly, where can I change the ANsi_NULL settings to OFF... Is it advisable..

Thank you guys

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 07:14:59

Don't feel bad, Fred! I posted AFTER you did, so I am even slower!

I was searching for a reference, found this page: http://msdn.microsoft.com/en-us/library/ms191504.aspx

"When null values are present in data, logical and comparison operators can potentially return a third result of UNKNOWN instead of just TRUE or FALSE. This need for three-valued logic is a source of many application errors"

But sniped not once, not twice, but three times, so I deleted it.

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2011-06-22 : 07:22:28

Could the ANSI setting be changed due to patches conducted on our servers ?
How to change the ANSI settings ? and it it ADVISABLE?

Thanks

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-23 : 04:09:49

SET ANSI_NULLS (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms188048.aspx

super_lucky_dog

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-06-23 : 04:53:27

No, it's not advisable. If you want the null results run


select * from dbo.table1
where C <> 'Yes' OR C IS NULL

or even



select * from dbo.table1
where COALESCE(C,'') <> 'Yes'

Or perhaps update your null columns to "No" if that's what they actually mean.

Think about it though; null means unknown. By writing NULL<>"Yes" then you are asking "is the unknown not equal to yes?". Well the unknown is unknown, so the comparison is impossible! So the I like to interpret the results of "False" as saying "false, you can't do this comparison". Makes far more sense than a "True" answer you'd get with ANSI_NULLS on. Plus, most people leave the behaviour alone, so it could confuse other developers to change it.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2011-06-23 : 04:59:24

From BOL http://msdn.microsoft.com/en-us/library/aa259229(v=sql.80).aspx

"The SQL-92 standard requires that an equals (=) or not equal to (<>) comparison against a null value evaluates to FALSE. When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name."

Not a good idea to change to something non-standard. You're asking for head aches later.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources