Re: So what's null then if it's not nothing?

Comments embedded within.

>> michael_at_preece.net wrote:
>>> I've been accustomed to thinking of things either having a value or
>>> not. If something has no value then, to me, its value is null. Its
>>> value is an empty string (whatever "it" is). Different to having a
>>> value of zero. Different to anything with a value. Now, as I read up
>>> on SQL, I find that null is supposed to mean "unknown". I can't
>>> easily accept that. Does SQL's definition of null (unknown) include
>>> the null I'm familiar with (no value)? That doesn't make sense. If
>>> we know something has no value then its not an unknown value is it?
>>> I can't imagine having to write code where the "if a=b then
>>> result=true else result=false" construct won't work - according to
>>> what I'm reading, if either a or b is null then I should be setting
>>> result to unknown instead. Just can't get my head 'round that.
>>> Shouldn't things be a lot simpler? If something has an unknown
>>> value then at least we know whether it's null (as in an empty
>>> string) or not. To me, "unknown" can be compared with an empty
>>> string to see if it's null or not. Sorry - head is spinning.

frosty suggested:
>> As a Pickie, I look at it like this:
>> It's spelled "null" but it's
>> pronounced "undefined." HTH.

Mike replied:
> Cheers for that Treank. It'd be amusing to sit back smugly and watch
> everyone make idiots of themselves over something so trivial if I
> didn't have to get involved myself. For us Pickies it's easy - null is
> equal to an empty string. There is no such thing as an unknown value.

Not so fast! There may be cases when you need to distinguish between an empty string and no string at all. There's a method to deal with this, called fifth normal form (5NF) I believe. I'll illustrate with some Pick code, if I may be so bold. Let's say an Contact has an attribute named "Sex" (just so folks will want to read this post =`:^> ) and there really is a difference between "M" "F" "" (the empty string) and "No Value Present for the Sex attribute for this Contact." Standard (Pick) practice would be to make Sex an attribute of the Contact file (let's say attribute 69) and you could imagine a (BASIC) code fragment to determine a Contact's Sex:
EQUATE CONTACT.SEX.ATTR TO 69
READV CONTACT.SEX FROM CONTACT.FV,CONTACT.ID,CONTACT.SEX.ATTR ELSE   CONTACT.SEX = ''
END You can see there's no way to distinguish an unknown/unassigned Sex from a Sex that is known, but happens to be defined as an empty string. (Remember the premise that we need to differentiate these two cases.) (Also, for you smart-ass Pickies out there, let's suppose there are more than 69 attributes in the Contact file. You'll see why in a second.)

5NF solution would be to put the Contact Sex into its own file (called Contact.Sex, say) in attribute 1 (because there's only one attribute in this file.)
EQUATE CONTACT.SEX.ATTR TO 1
READV CONTACT.SEX FROM CONTACT.SEX.FV,CONTACT.ID,CONTACT.SEX.ATTR ELSE   CONTACT.SEX = "!UNDEFINED!" ;! (or whatever you do in this case) END
This now gives you the ability to distinguish what you call "null" (an empty string) from what SQL calls "null" (an undefined value.)

> You can test any value against any other value and get a true or false
> result. No such thing as maybe in computing. A bit is either on or
> it's off. Simple. It's not just Pick that simplifies - or avoids
> unnecessary - confusion over this. Before I programmed in Pick I use
> various Assembler languages (IBM, NCR & ICL) and COBOL and never had
> any confusion over the concept of a mythical "unknown" value. Poor
> fools. Poor deluded fools. The sooner SQL-relational is seen for what
> it is - ridiculous - the better.
>
> Mike.
>
> PS. Strong enough you reckon?

Quite. (grins) You're a braver man than I! I don't think SQL should be called ridiculous (just) because it uses an unfortunate name ("null") to label something that we think of as "undefined." And, even though my example might not ring true, I don't think those seeking to distinguish empty values from unknown values are fools. I'll probably get flamed for my ignorance of SQL and 5NF; hate to imagine what they're gonna have to say about you, now. =`;^> Cheers.

-- 
frosty