What is (Null='Hello') in Sql Server
-
Great question even with the caveats posted in the comments. Thanks!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
ANSI_NULLS OFF is on deprecation list, so even if you can set it that way, is a good thing to avoid it.
And regarding the explanation, using ISNULL() on the WHERE clause will avoid the use of indexes for those columns.
-
When and why would it be useful to have ANSI_NULLS on?? Something is either null or it isn't to me. Having the system say "I can't tell you if a string literal is or isn't null" doesn't seem too useful.
From this I have learned that it is likely I have never worked in a database where ANSI_NULLS was on. I am thankful for that!
There is only one truth -
mikesyd (3/27/2014)
When and why would it be useful to have ANSI_NULLS on?? Something is either null or it isn't to me. Having the system say "I can't tell you if a string literal is or isn't null" doesn't seem too useful.From this I have learned that it is likely I have never worked in a database where ANSI_NULLS was on. I am thankful for that!
It would be useful to get used to it if you won't get the option available in the future. As it's useful for teenagers to learn how to drive even if their parents always drive for them.
NULL is not a value, is the lack of it. Handling nulls is something you should learn as they deserve a special treatment different to empty values.
-
mikesyd (3/27/2014)
When and why would it be useful to have ANSI_NULLS on?? Something is either null or it isn't to me. Having the system say "I can't tell you if a string literal is or isn't null" doesn't seem too useful.From this I have learned that it is likely I have never worked in a database where ANSI_NULLS was on. I am thankful for that!
I am going to guess that you probably have always worked with ANSI_NULLS ON.
Here is a classic example of why this behavior confuses people. When ANSI_NULLS are on anything that is compared to a NULL is NULL instead of true/false. This of course is an extension of how the bit datatype works. People tend to think of it as either 1/0, on/off or true/false. The reality is that there are 3 possibilities. The same is true with logical operators in sql. They can be true/false/NULL.
if OBJECT_ID('tempdb..#NullTest') is not null
drop table #NullTest
create table #NullTest
(
MyID int identity,
MyValue varchar(10)
)
insert #NullTest
select NULL union all
select 'ValuesHere'
declare @MyNull varchar(10) = NULL
set ANSI_NULLS ON --this is the default
select *
from #NullTest
where MyValue = @MyNull
set ANSI_NULLS OFF --this is NOT the default
select *
from #NullTest
where MyValue = @MyNull
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
This presented a nice angle on nulls -thanks, Jaime!
-
I know that. I also know (ok, I think I know) that an actual string literal is not null. It is not the absence of a value because it literally is a value. I can understand that try to compare something to nothing might be logically difficult, but I truly do not see the benefit of getting an "I dunno" answer when asking if something is or is not nothing.
There is only one truth -
I appreciate the replies. I understand the explanation. What I do not understand is why and/or how it would be useful to have the result of comparing something to nothing be 'unknown'. How is that ever useful? When I programmatically ask "Is there a value?", I don't imagine I'd ever want a 'maybe' in response. Maybe I'm just not imaginative enough!
There is only one truth -
Interesting QotD
Hope this helps...
Ford Fairlane
Rock and Roll Detective
-
mikesyd (3/27/2014)
I appreciate the replies. I understand the explanation. What I do not understand is why and/or how it would be useful to have the result of comparing something to nothing be 'unknown'. How is that ever useful? When I programmatically ask "Is there a value?", I don't imagine I'd ever want a 'maybe' in response. Maybe I'm just not imaginative enough!You don't get a maybe. If you want to know if there is a value you use IS NOT NULL. If you want a string that is not 'MyString' you would use <> 'MyString' which would return all NON NULL values that do not equal 'MyString'.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
"Hello" = NULL does not result in a FALSE - it results in NULL.
likewise NOT(NULL) is neither TRUE nor FALSE, it is NULL
-
I am going to try to pose this (same) question one more time. This is a simple question about the value of something that makes no sense to me. I am not asking what it is. I know what it is (now). I am asking WHY. Here is the question:
WHY would anyone ever want a logical operator to return a response other than true or false?
I'm looking for some elaboration on the benefits and super-cool advantages of having something like that and accepting it as the default - as well as easily reconciling yourself to the notion that in the future you will not have the option of a logical operator that will always give you a result of true or false. What is the good in that?
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.")? Presumably, the simple answer is because it is somehow useful. I'm asking HOW is that useful? I am asking WHY the default would be best as set ANSI_NULLS on rather than set ANSI_NULLS off, specifically with regard to a logical operator (in this case, the = operator) being thereby configured to return something other than true or false? I don't see how that is useful, but I have to believe that it truly is useful, or it wouldn't be the default. Also, I suspect people would be generally pretty upset about the prospect of not being able to set ANSI_NULLS off sometime in the future. Please someone explain why this is a very good thing and why it is actually better and preferred? I know that values can be null. I understand the use for that, and I understand that a null value isn't actually anything at all. It's not here. It's not there. It's not true false, 0, -1, 1, 17, or anything. What I do not understand is why we would want a logical operator to return anything other than true or false from a comparison.
There is only one truth -
AndyK-565224 (3/28/2014)
"Hello" = NULL does not result in a FALSE - it results in NULL.likewise NOT(NULL) is neither TRUE nor FALSE, it is NULL
Why is this desirable? How is it even logical, assuming of course that the = symbol in our discussion is always being used for comparison rather than assignment?
There is only one truth -
It is called Three-valued logic
https://en.wikipedia.org/wiki/Three-valued_logic
I do not understand enough in order to convince you, but I believe it is a way to deal with logical operations once you introduce the notion of NULL.
Does NULL equal TRUE? No.
Does NULL equal FALSE? No.
So how can something be both NOT TRUE and NOT FALSE at the same time? Because it is a third truth state=NULL
-
HeliMan (3/27/2014)
I appreciate the replies. I understand the explanation. What I do not understand is why and/or how it would be useful to have the result of comparing something to nothing be 'unknown'. How is that ever useful? When I programmatically ask "Is there a value?", I don't imagine I'd ever want a 'maybe' in response. Maybe I'm just not imaginative enough!First: Realise that there are a lot of misconceptions about NULL. NULL does not mean "Unknown" (as many people falsely assume). It simply means "There is no value here". Like an empty cell in a (pen and paper) data grid. There can be various reasons for that data to be missing, and the NULL itself does not carry any information on what the reason is, so the database should not infer. It simply does not have the data.
Now imagine that a criminal walks in your home, points a gun at your head, and says "You know Hugo Kornelis from SQLServerCentral, right? Now quick, answer this question: is he younger than 40? Refuse to answer, I shoot, Answer incorrect, I shoot. Answer correct, you live". What are you going to do?
I hope you are going to say "I have no idea" (well, unless you actually know my age). If the criminal keeps her word, you live - after all, you did not refuse to answer, nor did you give an incorrect answer.
In the database in your head, the value that would complete the sentence "Hugo Kornelis is ... years old" is missing. Why it is missing is irrelevant - maybe I once told you but you didn't record it; maybe I never told you, maybe it is highly classified and you had to pretend not to know, or maybe the age of Hugo Kornelis is not applicable because I am a very smart programmed 'bot that posts on SSC under a fake user name. Regardless of the cause, the result is the same - you can only answer the question whether I am younger than 40 with the answer "I don't know".
In database terms, the table Persons has a row identified by PersonName = 'Hugo Kornelis', and that row has value NULL in the column PersonsAge. So the predicate WHERE PersonsAge < 40 has to evaluate to Unknown. And the committee that defined the ANSI standard for SQL has decided that a predicate that evaluates to Unknown means that the row should not be included in the result set of the query.
Recommended reading: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/07/06/null-ndash-the-database-rsquo-s-black-hole.aspx (and the three follow-up posts; links are in the comments section).
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 - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply