Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


NULL


NULL

Author
Message
Pradyothana Shastry
Pradyothana Shastry
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1702 Visits: 481
Comments posted to this topic are about the item NULL


- Pradyothana


http://www.msqlserver.com
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21067 Visits: 18258
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

vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3494 Visits: 4408
"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"?
ziangij
ziangij
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2906 Visits: 374
easy one! :-)
Mahesh Bote
Mahesh Bote
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 1274
NULL is nothing but the placeholder.

--Mahesh


MH-09-AM-8694
Bob the Mushroom
Bob the Mushroom
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 24
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?
BrainDonor
BrainDonor
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2014 Visits: 11172
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.

Steve Hall
Linkedin
Blog Site
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8319 Visits: 11546
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?


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Tao Klerks
Tao Klerks
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1393 Visits: 1249
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.
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