Re: So what's null then if it's not nothing?
Date: Tue, 29 Nov 2005 16:55:55 +0100
In article <eaZif.6706$N45.5082_at_newsread1.news.atl.earthlink.net>,
> It comes as a surprise to me to learn that the SQL standard does not specify
> clearing NULLS from DISTINCT results. I agree that it's unfortunate, if
I don't quite understand what you mean by "clearing NULLs". Also, I must admit that I don't have access to the SQL standard as such, so to the degree that Oracle fails to implement SQL properly, I might be wrong.
> There *is* a workaround. You can always throw in a restriction, like
> WHERE XXX IS NOT NULL
> I admit that this has the flavor of a kluge,
I don't think so; no more than NULLs have the flavour of a kluge anyway. :) I think this sort of thing is a necessity, since the semantics of NULL is not based on logic, but on common agreement. If we use vc's example of a table of persons and countries, where country may be null, and ask about the number of people from Norway, what is the correct answer?
It depends. If NULL means that we don't know what country a person lives in, the database simply /does not know/. It knows the minimum number (the persons where country is Norway) and the maximum number (the minimum + the number of persons with NULL country), but to pick either answer as the correct one is a quite arbitrary value judgment.
In the presence of NULLs, a question such as "How many persons live in Norway?" is simply not specific enough. We need to ask "How many persons are we sure live in Norway?" or "How many persons may possibly live in Norway?". Adding in "IS [NOT] NULL" provides this disambiguation, and helps avoid the paradox that the sum of persons living in Norway and *not* living in Norway does not equal the total number of persons.
On the other hand, if NULL country means that the person does not live in a country, SQL will give the correct answer for the simple "How many persons live in Norway?" question---but the wrong one for the opposite, unless special care is taken. (And if that interpretation of NULL is the valid one, why are those persons in that table anyway? Probably because the DB designer is thinking in terms of entities instead of facts, which is a big mistake IMO. But now I guess I'm putting up straw men. :)
It is possible to live (and get correct results) with NULLs and 3VL, but you have to be very careful, and know exactly how it works. And it makes implementation of DBMSs and optimisation of queries very complex and difficult.
> Sorting pertains to cursors (read: lists) rather than sets. If a select
> forms a set with a NULL in it, then I expect ORDER BY to put it in the
> cursor somewhere.
Yes, and I hazard to guess you don't want the NULLs scattered about randomly, but collected neatly in a group, next each other. Which means they're equal with regard to sorting.
-- JonReceived on Tue Nov 29 2005 - 16:55:55 CET