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":
"184.108.40.206 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: http://sqlblog.com/blogs/hugo_kornelis