SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / Output of Query / Latest Posts

RE: Output of Query

Sunil Chandurkar — Fri, 31 Dec 2010 00:04:55 GMT

Thanks Hugo!Your comments help to learn more.

RE: Output of Query

cthomas1975 — Tue, 21 Dec 2010 02:42:26 GMT

Um, NO! You have inverted the condition on the <> '1' to = '1' with this "IN" statement.

RE: Output of Query

Bhuvnesh — Tue, 09 Nov 2010 00:24:27 GMT

Thanks to OP and special thanks to HUGO for great explanation.

RE: Output of Query

SQLRNNR — Tue, 02 Nov 2010 14:28:52 GMT

Thanks for the question.

RE: Output of Query

Hugo Kornelis — Thu, 28 Oct 2010 16:21:51 GMT

[quote][b]SanDroid (10/28/2010)[/b][hr][quote][b]Hugo Kornelis (10/28/2010)[/b][hr][quote]I wrote that NULL does not represent unknown.[/quote]I understand that is what you wrote. I thought maybe you would look at the referenced article before responding.[/quote]Since you didn't provide a reference and didn't indicate in any way that you wanted me to comment on the part you didn't quote, I could only assume that the quoted text was what you wanted me to comment on.[quote]Let me grab another cut and paste of the BOL that was referenced for the question."The value NULL means the data value for the column is unknown or not available."That is the very first sentance from the Books online: http://msdn.microsoft.com/en-us/library/ms191270%28v=SQL.100%29.aspx Now I understand that the Books Online have often been wrong.Do you believe they are in this instance?[/quote]Thanks for the reference. I have now read the entire article.I qualify it as sloppy, not as wrong. The first line you quote above is "sort of" correct. Since NULL represents missing data, "not available" is spot-on. The additional "unknown" in that sentence is superfluous, a bit like describing someone as "New Yorker or American" - unknown is a subset of missing value, as New Yorker is a subset of American.In the paragraph on camparing NULL values, I find this sentence, as an explanation of why NULL comparisons yield UNKNOWN:"This is because a value that is unknown cannot be compared logically against any other value."The "value is unknown" here is the old incorrect interpretation of NULL. But after the introductory paragraph above, this is easily identified as a sloppy reference, and it won't be taken as a change to the previous explanation.This kind of sloppiness annoys me. But I count my blessings - I've seen BOL pages that were much, much worse than this one.

RE: Output of Query

SanDroid — Thu, 28 Oct 2010 15:23:33 GMT

[quote][b]Hugo Kornelis (10/28/2010)[/b][hr][quote]I wrote that NULL does not represent unknown.[/quote]I understand that is what you wrote. I thought maybe you would look at the referenced article before responding.Let me grab another cut and paste of the BOL that was referenced for the question."The value NULL means the data value for the column is unknown or not available."That is the very first sentance from the Books online: http://msdn.microsoft.com/en-us/library/ms191270%28v=SQL.100%29.aspx Now I understand that the Books Online have often been wrong.Do you believe they are in this instance?

RE: Output of Query

Hugo Kornelis — Thu, 28 Oct 2010 15:05:31 GMT

[quote][b]SanDroid (10/28/2010)[/b][hr][quote][b]Hugo Kornelis (10/28/2010)[/b][hr]Good question, but a small (yet significan) mistake in the explanation.NULL does [b]not[/b] represent the value 'UNKNOWN'.[/quote]Hugo, perhaps you could ask M$ to update the books online to match your opinion?<Quote from BOL>When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:Copyytd_sales > NULLThe following comparison also yields UNKNOWN any time the variable contains the value NULL:ytd_sales > @MyVariable<Quote from BOL>[/quote]I wrote that NULL does not represent unknown. I also wrote that a comparison to NULL does result in the truth value Unknown.You quote an excerpt from BOL that states that comparisons in which one or more of the operands are NULL yield Unknown. I fail to see the mismatch between the BOL quote and my post.That being said, BOL does contain lots of errors - it is an excellent reference for the product SQL Server, but it is far from perfect when it comes to relational theory.[quote]Youre comment is correct with one small but significant correction.When ANSI_NULLS is OFF NULL is not value UNKNOWN.[/quote]I already admitted in a previous post that I never use ANSI_NULLS off. (And that no one should, really)

RE: Output of Query

SanDroid — Thu, 28 Oct 2010 13:04:33 GMT

Great question Sunil. Good example of how NULLS are counted in a column of data.I also give this question 5 stars for having no typos or confusion in the code, question, or answers!:cool:

RE: Output of Query

SanDroid — Thu, 28 Oct 2010 12:58:55 GMT

[quote][b]Hugo Kornelis (10/28/2010)[/b][hr]Good question, but a small (yet significan) mistake in the explanation.NULL does [b]not[/b] represent the value 'UNKNOWN'.[/quote]Hugo, perhaps you could ask M$ to update the books online to match your opinion?<Quote from BOL>When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, the following comparison always yields UNKNOWN when ANSI_NULLS is ON:Copyytd_sales > NULLThe following comparison also yields UNKNOWN any time the variable contains the value NULL:ytd_sales > @MyVariable<Quote from BOL>Youre comment is correct with one small but significant correction.When ANSI_NULLS is OFF NULL is not value UNKNOWN.

RE: Output of Query

UMG Developer — Thu, 28 Oct 2010 09:48:49 GMT

Thanks for the question!

RE: Output of Query

Daniel Bowlin — Thu, 28 Oct 2010 06:47:34 GMT

Regular reiteration of how nulls behave is very useful in QoTD. Thanks.

RE: Output of Query

Hugo Kornelis — Thu, 28 Oct 2010 03:14:57 GMT

[quote][b]vk-kirov (10/28/2010)[/b][hr][quote][b]Hugo Kornelis (10/28/2010)[/b][hr]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.[/quote]Setting this option wouldn't be enough, because it doesn't affect the comparison of a nullable column with a not-null value.[/quote]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)

RE: Output of Query

Richard Warr — Thu, 28 Oct 2010 03:09:54 GMT

[quote][b]Hugo Kornelis (10/28/2010)[/b][hr]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.[/quote]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.

RE: Output of Query

vk-kirov — Thu, 28 Oct 2010 02:58:42 GMT

[quote][b]Hugo Kornelis (10/28/2010)[/b][hr]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.[/quote]Setting this option wouldn't be enough, because it doesn't affect the comparison of a nullable column with a not-null value.[code="sql"]SET ANSI_NULLS OFF;GOSELECT NullableColumn FROM NullOperation WHERE NullableColumn <> '1';GO[/code][code="plain"]NullableColumn--------------0(1 row(s) affected)[/code]But the comparison of a column with NULL is affected:[code="sql"]SET ANSI_NULLS ON;GOSELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;GO[/code][code="plain"]NullableColumn--------------(0 row(s) affected)[/code][code="sql"]SET ANSI_NULLS OFF;GOSELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;GO[/code][code="plain"]NullableColumn--------------01(2 row(s) affected)[/code]

RE: Output of Query

sharath.chalamgari — Thu, 28 Oct 2010 02:29:19 GMT

simple Question,but the article that hugo has written are truly informative.

RE: Output of Query

Carlo Romagnano — Thu, 28 Oct 2010 01:36:43 GMT

-- non standard ansiSELECT NullableColumn FROM NullOperation WHERE ISNULL(NullableColumn,'') <> '1'OR-- standard ansiSELECT NullableColumn FROM NullOperation WHERE COALESCE(NullableColumn,'') <> '1'

RE: Output of Query

deepak.a — Thu, 28 Oct 2010 01:28:46 GMT

[quote][b]Hugo Kornelis (10/28/2010)[/b][hr][quote][b]deepak.a (10/27/2010)[/b][hr]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[code]SET ANSI_NULLS OFFGOSELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)GO[/code]it will depends on SET OPTION Reference [url] http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx [/url][/quote]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).[/quote]Sorry i made a mistake and thanks hugo for your explanantion

RE: Output of Query

Hugo Kornelis — Thu, 28 Oct 2010 01:14:35 GMT

[quote][b]deepak.a (10/27/2010)[/b][hr]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[code]SET ANSI_NULLS OFFGOSELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)GO[/code]it will depends on SET OPTION Reference [url] http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx [/url][/quote]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).

RE: Output of Query

Hugo Kornelis — Thu, 28 Oct 2010 01:05:20 GMT

Good question, but a small (yet significan) mistake in the explanation.NULL does [b]not[/b] 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:* [url=http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx]NULL - The database's black hole[/url]* [url=http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx]The logic of three-valued logic[/url]* [url=http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx]Dr. Unknown, or how I learned to stop worrying and love the NULL[/url]* [url=http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/30/what-if-null-if-null-is-null-null-null-is-null.aspx]What if null if null is null null null is null?[/url]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.

RE: Output of Query

deepak.a — Wed, 27 Oct 2010 23:12:22 GMT

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[code]SET ANSI_NULLS OFFGOSELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)GO[/code]it will depends on SET OPTION Reference [url] http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx [/url]

Output of Query

Sunil Chandurkar — Wed, 27 Oct 2010 22:20:29 GMT

Comments posted to this topic are about the item [B]Output of Query[/B]