March 30, 2014 at 6:07 pm
HeliMan (3/28/2014)
Why would I (or you, or anyone) actually APPRECIATE, much less graciously accept the programmatic question "Does x = null?" being answered with 'UNKNOWN' (e.g. "I'm sorry, Dave. I can't answer that.")?
Sorry, missed that part of your question in my first reply.
First: In the literal sense as presented here, getting true or false on an "= null" test would indeed make sense - but it would be inconsistent behaviour (more on that below). For the literal case IS NULL (and counterpart IS NOT NULL) is provided. Slightly more typing than = NULL or <> NULL, but still a lot less than the average COBOL program used to be.
Second: The consistency issue. Tests are often made between variables and/or columns instead of literals. So you might have a query that returns all people younger then me. You query my age, store it in a variable, then select people with Age < @HugosAge - or you use a subquery for the same effect in a single statement. So, what rows should be returned? All of them? None? Only some? No matter what answer you give here, the chance that you get it right is very small.
When the SQL language was designed, the creators had to find a standard answer for this question, that would be applied in all situations. Returning some rows makes the least sense, so there are only two viable conditions: return all rows where the predicate evaluates to Unkown (give the benefit of the doubt), or return none (prevent false positives). They chose the latter. The alternative would have been at least as confusing - maybe even more.
March 30, 2014 at 8:10 pm
[font="Tahoma"]Your example about the answer "i don't know" is very good (also very interesting), since i read that believe me when i say now i see the point of what is (Nulll='Hello') but you know why many people are asking or keep thinking why in sql this is like "UKNOWN", the reason is just because in our class of algorithm or program I, our teacher said that something if is not true, then is false (obvius), but when we tried to solve this QotD we confused the not(Null='Hello'). (At least it's happen to me).
After all, sql said that in future version SET ANSI_NULL will be always on so we must be carefully with this kind of cases but for me this specific case should throw execution time error.
don't blame me, i just think all programming languages should help to avoid unexpected situations. :-)[/font]
March 30, 2014 at 8:20 pm
jaimepc199 (3/30/2014)
the reason is just because in our class of algorithm or program I, our teacher said that something if is not true, then is false (obvius), but when we tried to solve this QotD we confused the not(Null='Hello'). (At least it's happen to me).
The teacher in the programming class told you that when he/she explained boolean (two-valued) logic. He/she was totally right. Two-valued logic is used in most, maybe even all traditional programming languages.
However, the teacher of the "relational databases" or "SQL" class should have taught you about this other logic system, that looks a bit like boolean but has been given a third value, that makes it behave differently. This three-valued logic will not be taught in the traditional programming classes, because traditional programming languages do not use the concept of NULL values (and hence have no need for three-valued logic).
April 2, 2014 at 12:38 pm
So amazing how long the SQL ANSI standards have been around and how little they are taught correctly or even understood for what they are.
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. Since then, the standard has been enhanced several times with added features. Despite these standards, code is not completely portable among different database systems, which can lead to vendor lock-in.
When ANSI NULL is ON any comparison of a NULL value to anything, including another NULL value, will always return nothing.
When ANSI NULL is OFF. Check the documentation for the specific version of the database system you are on and pray it is correct and up to date. 😉
April 6, 2014 at 1:10 am
Hi Hugo,
I just want to double check my understanding about NULL. Please explain more why NULL does not mean "Unknown". I thought that NULL mark represents missing or unknown data.
In this post, I explain how to solve this question of the day. It would be nice if you can provide some insight/suggestion/correction.
thanks
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
April 6, 2014 at 3:37 am
db4breakfast (4/6/2014)
Hi Hugo,I just want to double check my understanding about NULL. Please explain more why NULL does not mean "Unknown". I thought that NULL mark represents missing or unknown data.
In this post, I explain how to solve this question of the day. It would be nice if you can provide some insight/suggestion/correction.
thanks
Hi Hai Ton,
Short answer:
"3.1.1.12 null value
special value that is used to indicate the absence of any data value"
(quote from ANSI/ISO standard for SQL, document IWD 9075-1:201?(E), chapter 3.1: Definitions, available here)
Note that the definition only references the absence of a data value, loosely translated as missing value. There is no mention of unknown. A value can be missing because it is unknown, but there are other possible reasons as well. Most of the behavior of NULL makes sense when you think about NULL as "missing value". When thinking about it as "unknown", the logic sometimes falls apart. (Just as two buses with 42 children can be combined to a group of 84 children, but two shoes of size 42 when combined are not equal to a single size 84 shoe).
Long answer:
http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/17/the-logic-of-three-valued-logic.aspx
April 6, 2014 at 12:41 pm
Thanks Hugo.
I now understand NULL better and can conclude that NULL mark doesn't represent unknown data.
--------------------------------------------------------------------------------------
Hai Ton
My Db4Breakfast blog.
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply