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

On 1 Dec 2005 18:24:15 -0800, michael_at_preece.net wrote:

(snip)
> I do wish though that I could get them both to see
>clearly that missing data should be exactly that - missing, nowhere to
>be found in the database - and that the only valid use for NULL is to
>represent something that is known to be empty (whether the thing is a
>set or a scalar).

Hi Mike,

In Pick, it is. In SQL, it isn't.

In SQL, the only valid use for NULL is to represent that there is no data in a given cell.

(snip)
> Can Jon, or perhaps someone
>else, explain how an empty string differs from a NULL string? I don't
>want to know about a place-holder for a missing string - I mean a
>string that is known to be empty.

An "empty string", i.e. a string known to be empty, is a string that consists of 0 characters. In SQL code, this is represented as ''. In the internal storage of MS SQL Server, this is preresented by setting the pointer to the end of the string to the same location as the pointer to the start of the string. In C, this is represented by storing CHAR(0) at the first position of the character array used to represent the string. Other systems will probably have yet other ways to represent the empty string.

In string concatenation, '' behaves as 1 in multiplication: concatenate '' to anything, and whatever anything was is unchanged. I won't add other types of string manipulations to this list; I hope one example is enough.

A "NULL string" does not exist, at least not in SQL. But "NULL" does. It is a marker for the absense of a value. NULL itself is type-less, but it can be cast to any type. In SQL, NULL is represented as NULL. In the internal storage of SQL Server, this is represented by setting a bit in a seperate location; if this bit is set, SQL Server will disregard whatever bits and bytes happen to be lingering on the location where the column's value would normally be stored, and instead treat this column's value as "missing" (as defined by ANSI). In the internal storage of Oracle, NULL is represented by an "impossible value" from the domain of the column's datatype. I.e., for a numeric column, NULL is internally represented as -0, for a date column as some impossible date value, and for a string column as an empty string (*). Upon encountering these values, Oracle will treat the column's value as "missing" (as defined by ANSI). Other databases will probably have yet other ways to represent NULL. In string concatenation, NULL behaves as 0 in multiplication: concatenate NULL to anything, and whatever anything was, the result will always be NULL.
I won't add other types of string manipulations to this list; I hope one example is enough.

(*) Oracle's implementation is flawed, in that it uses the same internal representation for the empty string and for NULL. Once stored, there's no way to find out if the user meant NULL or ''. This is a design mistake; as long as they don't fix this, they will never be able to be fully ANSI compliant.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)