Re: does a table always need a PK?

"Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message news:3F553AD7.7C3_at_ix.netcom.com...
> Bob Badour wrote:
> >
> > "Lee Fesperman" <firstsql_at_ix.netcom.com> wrote in message
> > news:3F5452A0.1AB_at_ix.netcom.com..
> > > If I do a query on T and add (A+B) as a calculated attribute:
> > >
> > > select A, B, (A+B) as C from T;
> > >
> > > I get:
> > >
> > > A B C
> > > = = =
> > > 1 2 3
> > > 3 NULL NULL
> > > NULL 4 NULL
> > >
> > > Applying the SUM() function to each result attribute:
> > >
> > > select SUM(A), SUM(B), SUM(C) from (select A, B, (A+B) as C from T);
> > >
> > > produces:
> > >
> > > SUM(A) SUM(B) SUM(C)
> > > ====== ====== ======
> > > 4 6 3
> > >
> > > (SUM(A) + SUM(B)) is not equal to SUM(C)
> >
> > That's not what I would expect. I was under the impression SQL would
simply
> > omit any rows with a NULL in any SUM expression. In this case, I would
> > expect the sums to result in 1+2=3 preserving the identity. It is only
when
> > evaluating the sums separately that I would expect the identity to fail.
>
> I'm sorry. I don't follow you here.

Unless I am mistaken, the following queries return the following results:

Select SUM(A) from T;
SUM(A)

Select SUM(B) from T;
SUM(B)

Select SUM(A), SUM(B) from T;

SUM(A) SUM(B)
====== ======

1                2

The last result comes about by SQL discarding every row with a NULL that would affect the result. The first query includes 3 because the query does not reference B. The second query includes 4 because the query does not reference A. The third query includes neither because the query references both A and B. Does FirstSQL behave differently? Am I mistaken about SQL?

> > > Using coalesce():
> > >
> > > SUM(coalesce(A,0)) + SUM(coalesce(B,0)) =
> > SUM(coalesce(A,0)+coalesce(B,0))
> > >
> > > evaluates to True.
> >
> > Would you agree, then, that NULL and coalesce might place an additional
> > cognitive burden on the user ?
>
> Certainly. Though, I would argue that the additional complexity was
inherent when
> dealing with missing information. It then becomes a discussion of which
technique for
> missing information places the least cognitive burden on the user.

Fair enough. Or it could become a discussion of which technique for missing information most reliably alerts the user to the cognitive burden. Received on Wed Sep 03 2003 - 03:45:49 CEST