Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Output of Query Expand / Collapse
Author
Message
Posted Wednesday, October 27, 2010 10:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
Comments posted to this topic are about the item Output of Query
Post #1012049
Posted Wednesday, October 27, 2010 11:12 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, Visits: 863
thanks for the Nice Question :)

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


Post #1012071
Posted Thursday, October 28, 2010 1:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 5,969, Visits: 8,228
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
Post #1012101
Posted Thursday, October 28, 2010 1:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 5,969, Visits: 8,228
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
Post #1012107
Posted Thursday, October 28, 2010 1:28 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, January 2, 2014 9:57 AM
Points: 554, 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
Post #1012111
Posted Thursday, October 28, 2010 1:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 2,500, Visits: 2,381
-- non standard ansi
SELECT NullableColumn FROM NullOperation WHERE ISNULL(NullableColumn,'') <> '1'
OR
-- standard ansi
SELECT NullableColumn FROM NullOperation WHERE COALESCE(NullableColumn,'') <> '1'

Post #1012115
Posted Thursday, October 28, 2010 2:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 5:29 AM
Points: 1,179, Visits: 786
simple Question,

but the article that hugo has written are truly informative.

Post #1012128
Posted Thursday, October 28, 2010 2:58 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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)
Post #1012145
Posted Thursday, October 28, 2010 3:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:15 AM
Points: 2,114, Visits: 1,708
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.
Post #1012151
Posted Thursday, October 28, 2010 3:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 4:11 PM
Points: 5,969, Visits: 8,228
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

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 )

(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
Post #1012154
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse