SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Output of Query


Output of Query

Author
Message
Sunil Chandurkar
Sunil Chandurkar
Old Hand
Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)Old Hand (309 reputation)

Group: General Forum Members
Points: 309 Visits: 80
Comments posted to this topic are about the item Output of Query
deepak.a
deepak.a
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 863
thanks for the Nice Question Smile

To get the records with NULL value the WHERE condition needs to rewrite as WHERE NullableColumn <> '1' OR NullableColumn IS NULL.

For the above you can write like this also


SET ANSI_NULLS OFF
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)
GO




it will depends on SET OPTION
Reference http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11358 Visits: 12006
Good question, but a small (yet significan) mistake in the explanation.

NULL does not represent the value 'UNKNOWN'. NULL is a marker for a missing value, without any indication as to why the value is missing - the value being unknown is one of the possible reasons, but far from the only one; not applicable being the second-most common reason, and a whole bunch of less common reasons to follow.
Since a comparison with a missing value can never result in either of the truth values True of False, such comparisons will result in the third truth value, Unknown. So while NULL is not the same as Unknown, it does have the effect to make 1 <> NULL result in Unknown.

I have explained this in far more detail in a four-part series on my blog:
* NULL - The database's black hole
* The logic of three-valued logic
* Dr. Unknown, or how I learned to stop worrying and love the NULL
* What if null if null is null null null is null?

I must say that I am also very surprised (and disappointed) by the amount of incorrect answers. Almost 25% of respondents expect NULL to be returned as well - much more that I expected, because this is far from the first time that the effects of NULL in comparisons have been tested in the QotD.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11358 Visits: 12006
deepak.a (10/27/2010)
To get the records with NULL value the WHERE condition needs to rewrite as WHERE NullableColumn <> '1' OR NullableColumn IS NULL.

For the above you can write like this also

SET ANSI_NULLS OFF
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)
GO



it will depends on SET OPTION
Reference http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx

This is not equivalent; the original query excludes the value '1', whereas the IN clause includes '1' and NULL and excludes all others.
If you use SET ANSI_NULLS OFF, there is no need to rewrite anyway. The ANSI_NULLS affects all comparisons with NULL, not only those in a [NOT] IN expression.
And beware that the ANSI_NULLS OFF option is deprecated since SQL Server 2008. This option will be removed in a future version; don't build any new code that relies on this setting. (Though you should never have in the first placve, given how extremely non-standard this option is).


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
deepak.a
deepak.a
Right there with Babe
Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)Right there with Babe (782 reputation)

Group: General Forum Members
Points: 782 Visits: 863
Hugo Kornelis (10/28/2010)
deepak.a (10/27/2010)
To get the records with NULL value the WHERE condition needs to rewrite as WHERE NullableColumn <> '1' OR NullableColumn IS NULL.

For the above you can write like this also

SET ANSI_NULLS OFF
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)
GO



it will depends on SET OPTION
Reference http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx

This is not equivalent; the original query excludes the value '1', whereas the IN clause includes '1' and NULL and excludes all others.
If you use SET ANSI_NULLS OFF, there is no need to rewrite anyway. The ANSI_NULLS affects all comparisons with NULL, not only those in a [NOT] IN expression.
And beware that the ANSI_NULLS OFF option is deprecated since SQL Server 2008. This option will be removed in a future version; don't build any new code that relies on this setting. (Though you should never have in the first placve, given how extremely non-standard this option is).


Sorry i made a mistake and thanks hugo for your explanantion
Carlo Romagnano
Carlo Romagnano
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5111 Visits: 3327
-- non standard ansi
SELECT NullableColumn FROM NullOperation WHERE ISNULL(NullableColumn,'') <> '1'
OR
-- standard ansi
SELECT NullableColumn FROM NullOperation WHERE COALESCE(NullableColumn,'') <> '1'

I run on tuttopodismo
sharath.chalamgari
sharath.chalamgari
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1528 Visits: 798
simple Question,

but the article that hugo has written are truly informative.
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3750 Visits: 4408
Hugo Kornelis (10/28/2010)
If you use SET ANSI_NULLS OFF, there is no need to rewrite anyway. The ANSI_NULLS affects all comparisons with NULL, not only those in a [NOT] IN expression.

Setting this option wouldn't be enough, because it doesn't affect the comparison of a nullable column with a not-null value.
SET ANSI_NULLS OFF;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> '1';
GO


NullableColumn
--------------
0

(1 row(s) affected)


But the comparison of a column with NULL is affected:
SET ANSI_NULLS ON;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;
GO


NullableColumn
--------------

(0 row(s) affected)


SET ANSI_NULLS OFF;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;
GO


NullableColumn
--------------
0
1

(2 row(s) affected)

Richard Warr
Richard Warr
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2816 Visits: 1987
Hugo Kornelis (10/28/2010)
I must say that I am also very surprised (and disappointed) by the amount of incorrect answers. Almost 25% of respondents expect NULL to be returned as well - much more that I expected, because this is far from the first time that the effects of NULL in comparisons have been tested in the QotD.


Looking on the bright side it means that 25% of responders will have learnt something today. That's what this site is about as well. I suppose I get about half of the questions wrong, mainly on topics that I have never had to deal with as part of my work.

_____________________________________________________________________
MCSA SQL Server 2012
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11358 Visits: 12006
vk-kirov (10/28/2010)
Hugo Kornelis (10/28/2010)
If you use SET ANSI_NULLS OFF, there is no need to rewrite anyway. The ANSI_NULLS affects all comparisons with NULL, not only those in a [NOT] IN expression.

Setting this option wouldn't be enough, because it doesn't affect the comparison of a nullable column with a not-null value.

Woah! You are so right - thanks for correcting me. That'll teach e to comment on features I avoid like the plague w00t

And I also caught another error in my previous post. SET ANSI_NULLS is not deprecated since SQL Server 2008, but since SQL Server 2005. That means even less time to revisit old code that uses this feature (phew, am I glad I don't have any :-D)

(PS: Thank you for the kind words, sharath.chalamgari - I am glad you found the information useful)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
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