Re: OUTER JOIN vs UNION ... faster?

> SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
> FROM send0,card_info,category_details
> WHERE send0.card_id=card_info.card_id
> AND category_details.mcategory='e-cards'
> AND card_info.main_cat=category_details.category
> AND send_date >= '2001/04/08'
> AND send_date <= '2001/05/14' group by 1,2
...
> UNION ALL
>
> SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
> FROM send6,card_info,category_details where send6.card_id=card_info.card_id
> AND category_details.mcategory='e-cards'
> AND card_info.main_cat=category_details.category
> AND send_date >= '2001/04/08'
> AND send_date <= '2001/05/14' group by 1,2
>
> UNION ALL
>
> SELECT card_info.main_cat, category_details.sub_cat_flag,count(*)
>
> ========================================================================
>
> *Really* dreading the thought of changing it to an OUTER JOIN, and am
> wondering if there would be a noticeable speed difference between going
> from the UNION above to an OUTER JOIN, or should they be about the same?

afaict the point of this query is to do joins on separate tables send0
through send6. An outer join won't help you here. The last clause pulls
everything out of the other tables involved in the previous joins, so
I'm *really* not sure what stats you are calculating. But they must be
useful to have done all this work ;)

But if you had constructed those tables (or are they views?) to avoid an
outer join somehow, you could rethink that. An outer join on the two
tables card_info and category_details should be much faster than six or
seven inner joins on those tables plus the union aggregation.

- Thomas