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 12345»»»

NULL Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 9:08 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:58 AM
Points: 1,689, Visits: 455
Comments posted to this topic are about the item NULL


- Pradyothana


http://www.msqlserver.com
Post #902108
Posted Monday, April 12, 2010 9:09 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 18,055, Visits: 16,087
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
Post #902109
Posted Monday, April 12, 2010 11:58 PM
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
"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"?
Post #902141
Posted Tuesday, April 13, 2010 12:14 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, November 2, 2014 11:03 PM
Points: 2,130, Visits: 371
easy one!
Post #902145
Posted Tuesday, April 13, 2010 12:47 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:57 AM
Points: 1,098, Visits: 1,222
NULL is nothing but the placeholder.

--Mahesh


MH-09-AM-8694
Post #902154
Posted Tuesday, April 13, 2010 12:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 7, 2011 3:17 AM
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?
Post #902157
Posted Tuesday, April 13, 2010 1:01 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 3:35 AM
Points: 1,483, Visits: 8,541
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.


BrainDonor
Linkedin
Blog Site
Post #902160
Posted Tuesday, April 13, 2010 1:10 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #902165
Posted Tuesday, April 13, 2010 1:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 6,128, Visits: 8,393
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):
* 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
Post #902172
Posted Tuesday, April 13, 2010 1:17 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 12:38 PM
Points: 1,385, Visits: 1,249
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.
Post #902173
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse