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.

1_Difference between UNION and UNION ALL in SQL Server

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.

2_Difference between UNION and UNION ALL in SQL Server

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.

3_Difference between UNION and UNION ALL in SQL Server

 

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook