Re: Does Codd's view of a relational database differ from that of Date&Darwin? [M.Gittens]

From: Paul <paul_at_test.com>
Date: Wed, 08 Jun 2005 11:26:05 +0100
Message-ID: <42a6c7bd$0$32618$ed2619ec@ptn-nntp-reader02.plus.net>

mountain man wrote:

>>>Not exactly: SQL ignores NULLs in aggregate functions (except COUNT(*)).
>>>It is not treated as zero for AVG, for instance. Also note that x + NULL
>>>evaluates to NULL; therefore, SQL's SUM is not iterated addition -- it
>>>has a much more complicated definition.
>>
>>Thanks for the clarification.

>
> On the contrary, the answer is wrong for the MS TSQL.
> The AVG and SUM commands ignore nulls - they are
> not treated as *anything*, which in fact, they're not.
>
> And x + NULL does not evaluate to NULL but x.
> See the appropriate SET command in another post.
> Here the SQL SUM *is* iterated addition.

I'd rather the SUM of a column containing a NULL return NULL. If I want to override the behaviour, I can always do a SUM(COALESCE(col)). But if the default is to treat NULLs as zero, I can't manually override it to the behaviour I want.

At least MSSQL warns you that it's eliminated a NULL from an aggregate, although that's not much use if you're not running the query interactively.

And the default behaviour of pretty much all SQL DBMSs is that x + NULL is NULL. Maybe some obscure settings can override it but I doubt they are used much.

Paul. Received on Wed Jun 08 2005 - 05:26:05 CDT