Difference between UNION and UNION ALL is one of the most asked interview question.
UNION and UNION ALL both combine result set from two or more queries into single result. The difference is that UNION ALL doesn’t remove duplicate rows from the combined result set.
The below snapshot demonstrates the use of UNION operator.
The UNION operator combines the result from the two queries excluding the duplicate row with AddressID=1.
Let’s now replace UNION with UNION ALL and analyze the result.
The UNION ALL combines the result set including the duplicate rows as shown in above snapshot.
Having said this it is obvious that UNION ALL will perform better as it’s not doing the extra work of removing the duplicates. This is confirmed by comparing the execution plans of the two queries as shown in below snapshot.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
