Is there any difference between these two performance-wise?
-- eliminate duplicates using UNION SELECT col1,col2,col3 FROM Table1 UNION SELECT col1,col2,col3 FROM Table2 UNION SELECT col1,col2,col3 FROM Table3 UNION SELECT col1,col2,col3 FROM Table4 UNION SELECT col1,col2,col3 FROM Table5 UNION SELECT col1,col2,col3 FROM Table6 UNION SELECT col1,col2,col3 FROM Table7 UNION SELECT col1,col2,col3 FROM Table8 -- eliminate duplicates using DISTINCT SELECT DISTINCT * FROM ( SELECT col1,col2,col3 FROM Table1 UNION ALL SELECT col1,col2,col3 FROM Table2 UNION ALL SELECT col1,col2,col3 FROM Table3 UNION ALL SELECT col1,col2,col3 FROM Table4 UNION ALL SELECT col1,col2,col3 FROM Table5 UNION ALL SELECT col1,col2,col3 FROM Table6 UNION ALL SELECT col1,col2,col3 FROM Table7 UNION ALL SELECT col1,col2,col3 FROM Table8 ) x 3 Answers
The difference between Union and Union all is that UNION ALL will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.
A UNION statement effectively does a SELECT DISTINCT on the results set.
If you select Distinct from Union All result set, Then the output will be equal to the Union result set.
Edit:
Performance on CPU cost:
Let me explain with Example:
I have two queries. one is Union another one is Union All
SET STATISTICS TIME ON GO select distinct * from (select * from dbo.user_LogTime union all select * from dbo.user_LogTime) X GO SET STATISTICS TIME OFF SET STATISTICS TIME ON GO select * from dbo.user_LogTime union select * from dbo.user_LogTime GO SET STATISTICS TIME OFF I did run the both in same query window in SMSS. Lets see the Execution Plan in SMSS:
What happens is, The query with Union All and Distinct will take CPU cost more than Query with Union.
Performance on Time:
UNION ALL:
(1172 row(s) affected) SQL Server Execution Times: CPU time = 0 ms, elapsed time = 39 ms. UNION:
(1172 row(s) affected) SQL Server Execution Times: CPU time = 10 ms, elapsed time = 25 ms. So Union is much better than the Union All with Distinct in performance-wise
9Another on-point example that illustrates the four possible cases:
/* with each case we should expect a return set: (1) DISTINCT UNION {1,2,3,4,5} - is redundant with case (2) (2) UNION {1,2,3,4,5} - more efficient? (3) DISTINCT UNION ALL {1,2,2,3,3,4,4,5} (4) UNION ALL {1,1,2,2,2,3,3,4,4,5} */ declare @t1 table (c1 varchar(15)); declare @t2 table (c2 varchar(15)); insert into @t1 values ('1'),('1'),('2'),('3'),('4'); insert into @t2 values ('2'),('2'),('3'),('4'),('5'); select DISTINCT * from @t1 --case (1) UNION select DISTINCT * from @t2 order by c1 select * from @t1 --case (2) UNION select * from @t2 order by c1 select DISTINCT * from @t1 --case (3) UNION ALL select DISTINCT * from @t2 order by c1 select * from @t1 --case (4) UNION ALL select * from @t2 order by c1 Comparison between UNION DISTINCT and UNION ALL
This query is used to create an extended employee table with additional alternate IDs for downstream system. This example comes from a mySQL 8.0.20 enviornment.
For the data and query shown below, tests yielded a significant difference:
UNION ALL 8.983 sec UNION DISTINCT 15.344 sec To show scale and complexity for this example, table sizes and the query code are shown below
hqsource 600K records accountingemppos 180K accountingposld 200K emp_no_accountingnumeric 20 First UNION block is approx 550K records, second approx 50K SELECT a.`emp_no_imported` AS `emp_no`, a.`supervisor_emp_no`, a.`first name`, a.`middle name`, a.`last name`, a.`jobtitle`, a.`status`, CASE WHEN rida.`accounting_emp_no` IS NOT NULL THEN rida.`accounting_emp_no` ELSE a.`emp_no_imported` END AS `accounting_id`, CASE WHEN epfp.`emp_no` IS NOT NULL THEN CASE WHEN `sridf`.`emp_no` IS NOT NULL THEN `sridf`.`accounting_emp_no` ELSE epfp.`emp_no` END ELSE CASE WHEN epp.`emp_no` IS NOT NULL THEN CASE WHEN `srids`.`emp_no` IS NOT NULL THEN `srids`.`accounting_emp_no` ELSE epp.`emp_no` END ELSE CASE WHEN `srida`.`emp_no` IS NOT NULL THEN `srida`.`accounting_emp_no` ELSE a.`supervisor_emp_no` END END END AS `accounting_s_emp_no`, ep.`emp_no` AS `traas_emp_no`, epp.`emp_no` AS `traas_parent_emp_no` FROM `hqsource`.hq_people a LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `rida` ON `rida`.emp_no = a.`emp_no_imported` LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `srida` ON `srida`.emp_no = a.`supervisor_emp_no` LEFT OUTER JOIN `traas`.`accountingemppos_data_extract` ep ON ep.`emp_no` = a.`emp_no_imported` AND ep.`End` = '2899-12-31' AND ep.`Primary` = 'Y' LEFT OUTER JOIN `epe`.`accountingposld_data_extract` p ON p.`RangeGID` = ep.`GID` LEFT OUTER JOIN `traas`.`accountingemppos_data_extract` epp ON epp.`GID` = p.`ParentGID` AND epp.`End` = '2899-12-31' AND epp.`Primary` = 'Y' LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `rids` ON `rids`.emp_no = ep.`emp_no` LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `srids` ON `srids`.emp_no = epp.`emp_no` AND epp.`End` = '2899-12-31' AND epp.`Primary` = 'Y' LEFT OUTER JOIN `epe`.`accountingemppos_data_extract_filtered` epf ON epf.`emp_no` = a.`emp_no_imported` AND epf.`End` = '2899-12-31' AND epf.`Primary` = 'Y' LEFT OUTER JOIN `epe`.`accountingposld_data_extract` pf ON pf.`RangeGID` = epf.`GID` LEFT OUTER JOIN `epe`.`accountingemppos_data_extract_filtered` epfp ON epfp.`GID` = pf.`ParentGID` AND epfp.`End` = '2899-12-31' AND epfp.`Primary` = 'Y' LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `ridf` ON `ridf`.emp_no = epf.`emp_no` AND epf.`End` = '2899-12-31' AND epf.`Primary` = 'Y' LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `sridf` ON `sridf`.emp_no = epfp.`emp_no` AND epfp.`End` = '2899-12-31' AND epfp.`Primary` = 'Y' WHERE a.`emp_no_imported` REGEXP ('^[a-z]{2}\\d{5}.$') UNION ALL -- UNION DISTINCT SELECT a.`emp_no_imported` AS `emp_no`, a.`supervisor_emp_no` AS `s_emp_no`, u.`First_Name`, 'ƒ' AS `MI`, u.`Last_Name`, u.`Job_Title`, NULL AS `status`, CASE WHEN rid.`accounting_emp_no` IS NULL THEN ep.`emp_no` ELSE rid.`accounting_emp_no` END AS `accounting_emp_no`, CASE WHEN `srid`.`accounting_emp_no` IS NULL THEN epp.`emp_no` ELSE `srid`.`accounting_emp_no` END AS `accounting_s_emp_no`, ep.`emp_no` AS `traas_emp_no`, epp.`emp_no` AS `traas_parent_emp_no` FROM `epe`.`accountingemppos_data_extract_filtered` ep LEFT OUTER JOIN `hqsource`.`hq_people` a ON a.`emp_no_imported` = ep.`emp_no` LEFT OUTER JOIN `epe`.`accountingposld_data_extract` p ON p.`RangeGID` = ep.`GID` LEFT OUTER JOIN `epe`.`accountingemppos_data_extract_filtered` epp ON epp.`GID` = p.`ParentGID` LEFT OUTER JOIN `siebel`.`users_all_output` u ON u.`LOGIN` = ep.`emp_no` LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `rid` ON `rid`.emp_no = ep.`emp_no` LEFT OUTER JOIN `hqsource`.`emp_no_accountingnumeric` `srid` ON `srid`.emp_no = epp.`emp_no` WHERE ep.`End` = '2899-12-31' AND epp.`End` = '2899-12-31' AND p.`End` = '2899-12-31' AND ep.emp_no REGEXP ('^F\\d{8}$|^V[0-3]\\d{5}$') ORDER BY LENGTH(accounting_emp_no) ASC, accounting_emp_no ASC ;
The WHERE clauses in each of the two UNION blocks guarantee the results will be unique. (This query is years old and runs daily. I wish I had tried this sooner). Field names have been obfuscated
