Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Semantics of NULL in SQL Server 2008


The Semantics of NULL in SQL Server 2008

Author
Message
oscar.leeper
oscar.leeper
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 145

if(1=0 and 1=null)


Does ternary logic have the same rule that allows me to to say that the above is logically equivalent to

if 1!=0 or 1!=null

If so, I'm back where I started, since in my original example,

if 1=0 and 1=null

did not pass, but its logical equivalent,

if 1!=0 or 1!=null

does.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
oscar.leeper (9/2/2010)

if(1=0 and 1=null)


Does ternary logic have the same rule that allows me to to say that the above is logically equivalent to

if 1!=0 or 1!=null

Well, yes and no.
It is true that in ternary (three-valued) logic, just as in boolean (two-valued) logic, A and B is completely equivalent to not(not(A) or not(B)). But that not only means that if A and B, is true, not(A) or not(B) is false and if A and B, is false, not(A) or not(B) is true - it also means that if if A and B, is unknown, not(A) or not(B) is unknown as well, since not(unknown) is unknown.

If so, I'm back where I started, since in my original example,

if 1=0 and 1=null

did not pass, but its logical equivalent,

if 1!=0 or 1!=null

does.

The problem is that you use a simple IF to test things about ternary logic. But a simple IF has only two possible outcomes - one for true, one for both false and unknown. You would have gotten the expected results if you had used

if 1=0 and 1=null

and

if not(1<>0 or 1<>null)

But you omitted the not in the last one and switched the then and else clauses of the if. Which is fine for the truth values true and false, but not for the truth value unknown. If A is unknown, then NOT(A) is unknown as well, so that both "IF (A)" and "IF NOT(A)" will fall through to the else clause.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
frodriguez.im
frodriguez.im
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 16
Alex-668179 (8/26/2010)
AFAIK, NULL is the only way to leave a field empty!


I disagree: setting a field to NULL does not 'leave [or make] a field empty', instead it makes (or should make) any field value, and even the existence of such a value, undefined, inaccessible and irrelevant. That is what I meant before when I wrote before that NULL is a state: just stop thinking of NULL as a value!


I actually agree with most of that except for the "any value" part, a field cannot be any value or any logical operation on it would always return TRUE, replace any value with "an unknown value" and your definition is 100% correct.

The use of the term blank or empty field comes from thinking of the stage when the data is inputed, normally somebody will enter the data on a form and any fields that are unknown at the time (inaccessible) or not applicable (irrelevant) will be left blank or empty, when the data makes it to the database one way to express that the field was left blank is to set it to NULL, there may be many cases when normalization or some other method is a better choice, but in many cases using NULL is the best choice.
frodriguez.im
frodriguez.im
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 16
Hugo Kornelis (8/27/2010)
frodriguez.im (8/26/2010)
what is an empty value? AFAIK, NULL is the only way to leave a field empty!

For string columns and variables, the empty string is an often-used synonym for the zero-length string: ''.
I guess a varbinary could also be considered empty when the contents are zero-length. Other data types do not support an empty value, as there are no empty values in the various numeric domains, nor in the date, time, or datetime domains. (Maybe xml does support some kind of empty value, though I think you can only do that with untyped xml - but I am far from an expert in the field of xml, so I might be wrong).


I thought about that when I was writing my comment, my rationale for not mentioning it is that:

a) NULL is type independent and these are not so they're sort of not in the same context (in my head at least Smile); and
b) although they are widely accepted, in essence these are magic values so it's not really empty.

ps. you can use an empty string to set an "empty" xml field, you can also use an empty document header or any xml that doesn't really contains any data, either way when you query it and convert it to a string you get an empty string.
Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 460
I actually agree with most of that except for the "any value" part, a field cannot be any value or any logical operation on it would always return TRUE...


I don't understand your comment or perhaps you misread my comment or I misread yours. First of all I never said or suggested that a NULL field has "any value"; instead I said that "any value" should be ignored etc. because the field should be treated as not having a value. Second I disagree with the statement that even if I had implied that a NULL field had "any value", then every logical operation would return TRUE. Perhaps you are confusing this with a convention in mathematics which says exactly the opposite: that any statement about the elements of an empty set is TRUE.

The reason why I used so many words to say that the field should be treated as not having a value, is that apparently this simple statement is not enough. People still continue to discuss, for example, what NULL means for the value of the field (is it missing, or unknown, or what...) whereas such a discussion is totally inappropriate. Considering the mathematical background of the relational model the only interpretation of NULL that makes any sense to me is the minimalistic interpretation that there is no value. Period. Anything beyond that is too much speculation when it is done in discussing the meaning of NULL in general.
frodriguez.im
frodriguez.im
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 16
Alex-668179 (9/3/2010)
I actually agree with most of that except for the "any value" part, a field cannot be any value or any logical operation on it would always return TRUE...


I don't understand your comment or perhaps you misread my comment or I misread yours. First of all I never said or suggested that a NULL field has "any value"; instead I said that "any value" should be ignored etc. because the field should be treated as not having a value. Second I disagree with the statement that even if I had implied that a NULL field had "any value", then every logical operation would return TRUE. Perhaps you are confusing this with a convention in mathematics which says exactly the opposite: that any statement about the elements of an empty set is TRUE.

The reason why I used so many words to say that the field should be treated as not having a value, is that apparently this simple statement is not enough. People still continue to discuss, for example, what NULL means for the value of the field (is it missing, or unknown, or what...) whereas such a discussion is totally inappropriate. Considering the mathematical background of the relational model the only interpretation of NULL that makes any sense to me is the minimalistic interpretation that there is no value. Period. Anything beyond that is too much speculation when it is done in discussing the meaning of NULL in general.


I misread yours, I had too much to drink last night. If you had implied that the field could be any value then it could be the value that will fulfill the condition tested for and it would return TRUE but since it's not realistic that the field could have any value it's not worth discussing, my mistake.

Now how do you differ an empty or blank field from one with no value? I understand them to be the same.
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
frodriguez.im (9/4/2010)
Now how do you differ an empty or blank field from one with no value? I understand them to be the same.

In a varchar column, the empty value is '' - that is an opening and an ending quote with nothing in between. A great way to represent the middle initial of George Washington, as he had no middle initial.
In the same column, no value would be represented by NULL, the special marker to indicate the absence of a value. A great way to represent the middle intial of the 45th president of the United States, as noone currently knows what (if any!) middle initial he or she has.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Alex Fekken
Alex Fekken
SSC Veteran
SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)SSC Veteran (215 reputation)

Group: General Forum Members
Points: 215 Visits: 460
In my opinion a more informative comparison would be between NULL and empty string in the same field, e.g.middle name of George Washington. Empty string means "no middle name" (or a data error if there actually is one). NULL means that nothing is said about whether there is a middle name or not and what it's value might be if there is one.

That's the general idea but of course nothing stops people from giving their own interpretation to what NULL means in a concrete situation. But that would be a modification of the original NULL concept.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45253 Visits: 39934
Hugo Kornelis (9/4/2010)
frodriguez.im (9/4/2010)
Now how do you differ an empty or blank field from one with no value? I understand them to be the same.

In a varchar column, the empty value is '' - that is an opening and an ending quote with nothing in between. A great way to represent the middle initial of George Washington, as he had no middle initial.
In the same column, no value would be represented by NULL, the special marker to indicate the absence of a value. A great way to represent the middle intial of the 45th president of the United States, as noone currently knows what (if any!) middle initial he or she has.


Exactly! Hugo for President! :-)

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search