April 12, 2010 at 9:08 pm
Comments posted to this topic are about the item NULL
April 12, 2010 at 9:09 pm
Nice refresher question.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2010 at 11:58 pm
"NULL does not mean NULL" – this option sounds very strange to me. The explanation says "it does not mean '' (empty string)". Should the first option be "empty string" instead of "NULL"?
April 13, 2010 at 12:14 am
easy one! 🙂
April 13, 2010 at 12:47 am
[font="Verdana"]NULL is nothing but the placeholder.
--Mahesh[/font]
MH-09-AM-8694
April 13, 2010 at 12:53 am
Speaking as someone who opted for NULL instead of UNKNOWN, that explanation was terrible. Why doesn't NULL mean NULL, and what did empty strings have to do with anything? Was the question asking what NULL means in the context of a string variable?
April 13, 2010 at 1:01 am
Bob the Mushroom (4/13/2010)
Speaking as someone who opted for NULL instead of UNKNOWN, that explanation was terrible. Why doesn't NULL mean NULL, and what did empty strings have to do with anything? Was the question asking what NULL means in the context of a string variable?
It could have possibly have been phrased slightly better ("The fixed value NULL", or something like that), but it is correct. NULL does not equal NULL. Try comparing two NULL values in SQL - they will never be equal. A NULL field is actually unknown, so anything involving a NULL has an unknown value. Endless hours of fun for the unwary.
Have a look at 'about NULL values' in BOL.
BrainDonor.
April 13, 2010 at 1:10 am
Sorry, but within the context of Microsoft terminology, this is simply wrong.
Within the context of SQL Server documentation, "UNKNOWN" (all uppercase) is used exclusively as a Boolean result in SQL Server's 3-valued "Boolean" logic:
http://msdn.microsoft.com/en-us/library/ms188074.aspx
And no, the "UNKNOWN" boolean result is NOT the same as "NULL" (even though with ANSI_NULLS on, any comparison with NULL will yield an "UNKNOWN" Boolean result).
In terms of natural language it is reasonable to say that "Null means unknown", but Microsoft's own explanation is "The presence NULL usually implies that the value is either unknown or undefined", which is a much more carefully qualified statement - implying that the only correct answer from the options given is "Null means Null"...
I'm sorry, but I don't even understand what the author intended/tried to demonstrate or test for with this question - maybe that a definition should not include the term being defined??? If so it would be worth mentioning that in the explanation!
(EDIT: changed "boolean" to "Boolean" to better reflect that I am talking about SQL Server's data type rather than a true "boolean" logic, which would by definition only have two possible values)
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 13, 2010 at 1:17 am
Bad question. Or maybe I should say, great question, terrible answer options. I only got a point because I (rightly) assumed that the question's author fell victim to the most common misunderstanding about NULL.
Let's start at the authorative source - the ANSI/ISO standard for SQL. Here is a citation from SQL-2003, from "Defnitions and use of terms":
"3.1.1.11 null value: A special value that is used to indicate the absence of any data value."
And in the next chapter, "Concepts":
"4.4.2 The null value
Every data type includes a special value, called the null value, sometimes denoted by the keyword NULL. This value differs from other values in the following respects:
— Since the null value is in every data type, the data type of the null value implied by the keyword NULL cannot be inferred; hence NULL can be used to denote the null value only in certain contexts, rather than everywhere that a literal is permitted.
— Although the null value is neither equal to any other value nor not equal to any other value — it is unknown whether or not it is equal to any given value — in some contexts, multiple null values are treated together; for example, the <group by clause> treats all null values together."
So, NULL does not represent unknown, but missing value - what is unknown is the outcome of comparisons to other values, not the value itself.
Why is this distinction important? Because NULL is not only used when an value is missing because it is unknwon, but also when an attribute is missing because it is not applicable, because it will be supplied later, or any other reason. If a company that does business with both natural and legal persons, the "DateOfBirth" column will be NULL for legal persons - and this is not because we all forgot when the company was born! Saying the NULL means Unknown would preclude that use of NULL.
The explanation also makes no sense at all!
"The value NULL means UNKNOWN; it does not mean '' (empty string)."
Ah, so the correct answer is UNKNOWN becauuse it is UNKNOWN. Makes sense. The empty string option was not given. The two other incorrect options are not mentioned at all in the explanation.
"Assuming ANSI_NULLS are on in your SQL Server database, which they are by default,"
Okay, I admit it - this snippet in the explanation is actually correct.
"any comparison to the value NULL will yield the value NULL."
No. A comparison to the Null value will return the value UNKNOWN. The value UNKNOWN is a different value from the value NULL. The value UNKNOWN is the third value in three-valued logic (the other two being TRUE and FALSE). In SQL Server, none of the resutls of three-valued logic can be assigned to a variable, but all predicates evaluate to one of these three values. If a predicate is used in a WHERE, HAVING or IF, the row, group, or conditional action will only be included or executed if the predicate evaluates to TRUE. If a predicate is used in a CHECK constraint, a modification will only be permitted if it evaluates to TRUE or UNKNOWN.
"You cannot compare any value with an UNKNOWN value and logically expect to get an answer."
Using the correct meaning of UNKNOWN, I agree. You can not compare any value with UNKNOWN, as SQL Server has no way to store or manipulate the result of a predicate. Neither can you compare any value with TRUE or FALSE.
But as the author intends it, based on the incorrect assumption that NULL is UNKNOWN, this is not correct. You can compare any value with a NULL. And you can logically expect an answer. The answer to expect is even explicitly stated in the ANSI standard - any comparison with a NULL will always return UNKNOWN.
Four links for further reading (all point to articles on my blog - apologies for the self-plug, but I still haven't found anyone I agree more with than me:-D):
* 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?
April 13, 2010 at 1:17 am
BrainDonor (4/13/2010)
It could have possibly have been phrased slightly better ("The fixed value NULL", or something like that), but it is correct. NULL does not equal NULL. Try comparing two NULL values in SQL - they will never be equal. A NULL field is actually unknown, so anything involving a NULL has an unknown value. Endless hours of fun for the unwary.
The sentence "NULL does not equal NULL" is correct, when you have ANSI_NULLs on...
On the other hand, the statement "Try comparing two NULL values in SQL - they will never be equal" is just wrong. Try this:
SET ANSI_NULLS OFF
IF Null = Null
PRINT 'Oops, Null equals Null today!'
The Phrase "A NULL field is actually unknown, so anything involving a NULL has an unknown value" is reasonable. From that to "[the question] is correct" is a long stretch;
The question did not ask "What does Null Equal" (and even then "UNKNOWN" would not have been an accurate answer!) - it's simply a terrible terrible question, there's nothing "correct" about it.
(Edit: had my booleans wrong, further confusing things)
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 13, 2010 at 1:18 am
Sorry: NULL IS NULL.
April 13, 2010 at 1:19 am
Hah, should have known Hugo would come explain everything properly - ignore my posts, concentrate on his 🙂
http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.
April 13, 2010 at 1:37 am
I got my point because I could guess the spirit of the question from the "UNKNOWN" option, but it's plain wrong.
NULL := NULL
-- Gianluca Sartori
April 13, 2010 at 2:26 am
Hugo Kornelis (4/13/2010)
[wrote lots of good stuff...]
Thanks for this excellent clarification which deserves an Article on its own! So you better get started! 😀
Best Regards,
Chris Büttner
April 13, 2010 at 2:37 am
Christian Buettner-167247 (4/13/2010)
Hugo Kornelis (4/13/2010)
[wrote lots of good stuff...]Thanks for this excellent clarification which deserves an Article on its own! So you better get started! 😀
Thanks, Chris!
I already wrote and published that article some years ago. Just not here, but on my blog. See the links in my first post.
Viewing 15 posts - 1 through 15 (of 67 total)
You must be logged in to reply to this topic. Login to reply