Union vs. Union ALL if rows are unique

Question

I would like to optimize performance while bringing together queries on many SAS data sets with the same metadata. At this point I have: select * from (select t1.column_a, t1.column_b from table t1) Union (select t2.column_a, t2.column_b from table t2) and so on. Each query brings up unique rows, do I save time wise if I use use Union All instead?

What is the correct **result**? If, unlike your assertion, there *are* duplicate results, should they be in the resultset? That should be your entire criteria. — Damien_The_Unbeliever, Jun 28 '16 at 19:06

score 1 · Answer 1 · edited May 23 '17 at 11:58

1

Yes. you are correct. Please refer this. What is the difference between UNION and UNION ALL?

If you are pretty sure that you don't have duplicates, then you can just use the UNION ALL instead of UNION. The later lacks in performance as it has to remove the duplicates

edited May 23 '17 at 11:58

Community

1
1

answered Jun 28 '16 at 19:00

Tharsan Sivakumar

6,351
3
19
28

score 0 · Answer 2 · answered Jun 28 '16 at 18:58

At some point during UNION there will be checks for duplicates. Even if these checks all turn up false, they are an extra step. UNION ALL will probably be more efficient but as dfundako pointed out, you'll have to test and see to be sure of a difference in speed.

Union vs. Union ALL if rows are unique

2 Answers2