
Got a query that looks like:

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

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 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


Tue, 24 Apr 2001 09:06:29 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
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

Tue, 24 Apr 2001 16:31:20 +0000
Thomas Lockhart <lockhart@alumni.caltech.edu>
Re: OUTER JOIN vs UNION ... faster?