Thread: OUTER JOIN vs UNION ... faster?

Got a query that looks like:

========================================================================

SELECT card_info.main_cat, category_details.sub_cat_flag,count(*) FROM send0,card_info,category_detailsWHERE
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 send1,card_info,category_details where
send1.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 send2,card_info,category_details where
send2.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 send3,card_info,category_details where
send3.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 send4,card_info,category_details where
send4.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 send5,card_info,category_details where
send5.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?


Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

> 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