Re: Does Codd's view of a relational database differ from that of Date&Darwin? [M.Gittens]
Date: Wed, 08 Jun 2005 11:26:05 +0100
Message-ID: <42a6c7bd$0$32618$ed2619ec_at_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.
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 - 12:26:05 CEST