April 2, 2014

Union and Union all - SQL Server

I have come across many times use of this two function Union and Union all, so I thought of writing difference between these two operators.

Both Union and Union all combines rows of 2 or more than two tables into one. Main difference between Union and Union all is Union helps to remove duplicate rows. It takes time to execute the query because it has to perform distinct operation But, Union all pulls all the rows from all the tables so it takes less time to execute the query.

Example:

SELECT * FROM TABLE1

ID
1
2
3

SELECT * FROM TABLE2

ID
2
3
4

SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2

ID
1
2
3
4

SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2

ID
1
2
3
2
3
4 

Sorting the result of Union and Union all

SELECT * FROM TABLE1
UNION ALL
SELECT * FROM TABLE2
ORDER BY ID

ID
1
2
2
3
3
4

No comments: