Union and Union All in Sqlserver

Author : Prakash Pradeep Gopu

A Union/Union All query combines the two or more queries and the result are returned in to a single result set.

Syntax :

Select <col1>,<col2>,<col3> from <table1>

Union/Union All

Select <col1>,<col2>,<col3> from <table2>

Create following two tables in the Sqlserver :

Table1
ID	Name
1	Prakash
2	Pradeep
3	Sathya
4	sandya

Table2
ID	Name
1	Prakash
2	Pradeep
3	Harsha
4	spandana

Execute the following query in the Sqlserver will give the following result :

Select * from Table1

Union

Select * from Table2

It will display the following result : Total count –6 .

ID	Name
1	Prakash
2	Pradeep
3	Harsha
3	sathya
4	sandeep
4	Spandana

Execute the following query in the Sqlserver will give the following result :

Select * from Table1

Union All

Select * from Table2

It will display the following result : Total count –8 .

ID	Name
1	Prakash
2	Pradeep
3	Harsha
4	Spandana
1	Prakash
2	Pradeep
3	sathya
4	sandeep

 

Difference between Union and Union All:

The difference between Union and Union all is that Union all will not eliminate duplicate rows,  instead of it just pulls all rows from all tables and combines them into a table.