Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

UNION ALL and SUM vs JOIN

928781Oct 10 2012 — edited Nov 13 2012
It was proposed to me that instead of joining 2 tables, it could increase performance in some cases to union them together and sum their results.

--NORMAL JOIN
SELECT a.emp_no, a.dept_no, b.salary
FROM employee a, payroll b
WHERE a.emp_no = b.emp_no
;
--UNION ALL and SUM
SELECT emp_no, SUM(dept_no), SUM(salary)
FROM (
   SELECT emp_no, dept_no, 0 salary
   FROM employee
   UNION ALL
   SELECT 0, 0, salary
   FROM payroll
   )
GROUP BY emp_no
;
I was given only anecdotal evidence for this method improving performance, but it does not seem completely unbelievable. In what case would one of these methods be preferable to the other?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 11 2012
Added on Oct 10 2012
8 comments
1,062 views