> Oh, I wasn't thinking about whether you use select * or
> select <column
> name>. This bit was advising to add WHERE <some criteria> to
> reduce the number of rows returned.

Point taken. Thanks.

> >From my limited testing, the performance is quite good, around 2
> >seconds. Now this baffles me since I was told once that 'correlated
> >subquery' could not be faster than a straightforward join...
> Whomever said this probably meant [INNER] JOIN, not LEFT (or
> RIGHT) [OUTER] JOIN.

That must be it...:)

> I didn't suggest this because of the speed, but because your
> original query may or may not give correct results.
> Your original query asked for all
> records in the product table and then left joined
> ordermaster. The where
> clause is used for restricting returned rows and by putting
> something from
> ordermaster there, you are at the same time telling Fb to return all
> records of product and reduce it to those who link to a record with
> ordermaster.transdate between two dates. Basically, this is
> telling Fb to
> only give you those rows of ordermaster having a transdate
> between your
> dates, but at the same time let Fb itself choose whether it
> wants to return
> all records of product or only those who link to an
> ordermaster with the correct dates.

It took me two days to digest your explanation, but I think I
(vaguely...:) understand your point. Thank you for spelling it out for
me...:) It was much simpler in sql-89 (i.e. lump everything in the where
clause and let poor firebird sort it out)...:)

Actually, I think this is a recurring mistake from people moving from
sql-89 to sql-92 since most of the articles talking about the new JOIN
syntax don't go deeper into the implications. Almost all the books I
have on sql-92 stop on two table joins. Nothing is said on three table
joins or more, and how to correctly formulate them. I'm wondering if
anyone can point me to any articles on the subject available on the
internet.... thanks in advance.

> In this case you are only doing an inner join between OrderDetail and
> OrderMaster and your WHERE clause is for the subselect. Here,
> I don't see the point in having the m.orderdate within the join -
> I would have put it in the where clause.

I was confused for a while until I realized that you meant the where
clause of the subquery, not the main query....:) The revised query is :
...
SELECT
p.productID,
(SELECT SUM(o.qty)
FROM OrderDetail o join ordermaster m
on (o.ordermasterid = m.ordermasterid)
where m.orderdate between '2003-01-01' and '2003-01-02' )
WHERE o.productid = p.productid)
FROM
Product p
...

Noted. Thank you, mr Tysvaer.
Regards,
Sugi.