NULL

  • Pradyothana Shastry

    SSCertifiable

    Points: 5211

    Comments posted to this topic are about the item NULL

  • SQLRNNR

    SSC Guru

    Points: 281106

    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

  • vk-kirov

    SSCertifiable

    Points: 7686

    "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

    SSCertifiable

    Points: 6514

    easy one! πŸ™‚

  • Mahesh Bote

    SSCrazy Eights

    Points: 8997

    [font="Verdana"]NULL is nothing but the placeholder.

    --Mahesh[/font]

    MH-09-AM-8694

  • Bob the Mushroom

    Valued Member

    Points: 65

    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

    SSCoach

    Points: 19153

    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

    SSCarpal Tunnel

    Points: 4187

    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

    SSC Guru

    Points: 64645

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4187

    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.

  • honza.mf

    SSCertifiable

    Points: 5519

    Sorry: NULL IS NULL.



    See, understand, learn, try, use efficient
    Β© Dr.Plch

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4187

    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.

  • spaghettidba

    SSC Guru

    Points: 105661

    I got my point because I could guess the spirit of the question from the "UNKNOWN" option, but it's plain wrong.

    NULL := NULL

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Viewing 15 posts - 1 through 15 (of 68 total)

You must be logged in to reply to this topic. Login to reply