What is the Difference in Union and Union All

In this article, we are going to discuss about the most asked SQL interview question – What is the difference between Union and Union All.

Table of Content

What is Union and Union All?

Union and Union All is a set of SQL operators to join two or more results set using the select queries. However, they have a fundamental difference.

Union

1. Union operator join the result sets of multiple queries while removing duplicate records. 
2. Union perform a distinct selection on the combined result sets, ensuring that only unique and distinct rows are being processed in the final result set. 
3. If Table 1 and Table 2 contain the same number of column, union will return distinct values.

Union SQL Code

SELECT column1 FROM table1 
UNION 
SELECT column1 FROM table2; 

Union All

1. Union all – also does the same operation as Union. However, it combines the result sets of multiple queries and retains all rows, including duplicates records.
2. The only difference is the Union All does not perform a distinct operation, and it includes all rows from each query in the final result set. 
3. In the below code union all will return all rows from both table 1 and table 2, including the duplicate and works faster as compared to Union as it’s not performing a distinct operation on the result set.

Union All SQL Code

SELECT column1 FROM table1 
UNION ALL 
SELECT column1 FROM table2; 

Summary

If the business requirement is to fetch only distinct records from the result set, it’s advisable to use Union. Just in case if you want to retain all the records from combining two or more select queries it’s advisable to use Union All.

Note

Sometimes interviewer may ask, can we perform the Union and Union all SQL operation on two different table structure which is having different column sequence. The answer to this question is “Yes”, we can perform the operation by selecting the column in the select query and by defaulting the non-existent column as NULL. Also, considering the datatype is same in both the tables.