[Info-Ingres] Few questions concerning Ingres 2006

[Karl & Betty Schendel]

At 7:38 PM +0000 4/10/07, chris at usenet.invalid.mmleo.com wrote:
>On Tue, 10 Apr 2007 08:21:08 +0100, "Roy Hann"
><specially at processed.almost.meat> wrote:
>
> >select p.*,x.total
>>from parts p
>>join
>>(  select partnr,sum(qty_on_hand) as total
>>   from parts_onhand
>>   group by partnr) x
> >on p.partnr = x.partnr



>Roy, Unless I am missing something, do you not get the same results
>from:
>	select p.*, total=IFNULL(SUM(po.qty_on_hand), 0)
>	from parts p LEFT JOIN parts_onhand po ON p.partnr = po.partnr
>	group by p.partnr, p.col2, p.col3

Roy would get the same answer, since I know what his data
looks like;  but the two queries are not equivalent.
(Not even if you use JOIN instead of LEFT JOIN.)

The difference arises if parts.partnr is not unique.
The subquery version sums qty_on_hand by parts_onhand.partnr first,
and if there are two parts rows with that partnr, we simply see the
sum twice.
The join version may produce one row with a doubled sum
if the other grouping columns are also identical.  (Yes, that
would be pretty bogus data, but it must be considered.)

I certainly believe that FROM-clause subqueries are
overused when they are available;  I've seen any number
of garish other-RDBMS queries where flattening could have
been used just as well.  However, one must take care to
get the right answer first.  (Which was Roy's point,
after all.)

Karl