[HACKERS] OUTER JOIN vs UNION ... faster?

The Hermit Hacker Tue, 24 Apr 2001 04:50:34 -0700

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: [EMAIL PROTECTED]           secondary: scrappy@{freebsd|postgresql}.org


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] OUTER JOIN vs UNION ... faster?

Reply via email to
