SQL Union Vs UnionAll

UNION

- The UNION command is used to select related information from two tables, much like the JOIN command. 
- However, when using the UNION command all selected columns need to be of the same data type. 
- With UNION, only distinct values are selected.


UNION ALL

- The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.
- 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 know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.



1. Employee table data

2. Customer table data

UNION Example (It removes all duplicate records)

UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION)

Summary

In this article I try to explain the differences of UNION and UNION ALL in SQL Server with a simple example. I hope after reading this article you will be able to understand cursors in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.