Here we will see the major difference between union and union all with practical examples. After reading this article you will have a very clear understanding of this topic.
It is one of the commonly asked interview questions. One should answer this because it is a very basic concept of SQL.
We often confuse when an interviewer asks some scenario-based queries on this topic.
Let us see this with a real-time example.
Major Differences
In simple words we can say that these two operators are used to combine the result set of two are more select queries.
We have two tables here TBL Indian customers and TBL UK customers. Both of the tables have identical columns which are ID’s and emails.
Just observe the below SQL queries. If I execute them I will get two different SQL statements as shown in the results set.
I got two rows from both the tables as two different data sets.
First, we will see UNIONALL.
When I execute the UnionALL query I got four records including duplicates. Just see the below results Sam record got duplicated.
Now let us execute the Union_Query. Just change operator alone.
This SQL query has returned three rows with sorting. Here we have got one Sam record.
It is doing two things here.
- Removing Duplicates
- Sorting the Results
In the context of group by when you are writing queries with SQL union_group by or SQL union_all groups by make sure you use group by at the end otherwise it will through an error.
Union vs UnionAll Performance
To remove duplicate records union has to perform a distinct sort, which makes it less fast than other one.
Let us see the execution plan here.
Look at the above diagram distinct sorting is taking more than 63% of the time .
See the execution Plan here. It runs quickly.
What we have learned?
UNION and UNIONALL are SQL operators wont to concatenate 2 or more result sets. this enables us to write down multiple SELECT statements, retrieve the specified results, then combine them together into a final, unified set.
UNION: only keeps unique records
UNIONALL: keeps all records, including duplicates
UNIONALL keeps all of the records from each of the first data sets, UNION removes any duplicate records.
Summary
One performs a deduplication step before returning the ultimate results, the other retains all duplicates and returns the complete, concatenated results.
Difference Between UNION and UNION beat SQL Server
Both the operators are wont to combine the result set of two or more SELECT statements into one result set. However, there’s a small difference in the working functionality of SQL Server, and therefore the result set:
Performance of the Union is slightly less than the UnionALL operator because it’s to see for the duplicates (which may be a time-consuming process)