Union vs Union All in SQL

Union vs Union All in SQL

In many situation you want to show data of  two or more tables in a single Select Statement 

Like You have two table Employee1 and Employee2

create table Employee1

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee1 values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000')

select * from Employee1

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

create table Employee2

(Empid int identity (1,1) ,EmpName nvarchar(50),EmpSalary float)

insert into Employee values('Amit','5000'),('Sumit','6000'),('Raj','8000'),('vijay','9000'),('suresh','10000'),('arun','9500'),('akash','10500')

select * from Employee2

Output

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay        9000

5 suresh     10000

6 arun        9000

5 akash      10500

Now you want to merge all the name ,salary present in both the Tables

for this use Union All 

--All  Rows from Table Employee1 and Employee2

select * from Employee1

union all

select * from Employee2

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj         8000

4 vijay         9000

5 suresh 10000

1 Amit          5000

2 Sumit 6000

3 Raj       8000

4 vijay           9000

5 suresh 10000

6 arun          9500

7 akash 10500

IF you want to Display Only Distinct Data in Both the Tables i.e. Means No reapeated Rows 

then Use Union

--All distinct Row from Table Employee1 and Employee2

Empid EmpName EmpSalary

1 Amit         5000

2 Sumit 6000

3 Raj        8000

4 vijay          9000

5 suresh 10000

6 arun           9500

7 akash 10500

Performance wise Union All  Operator is Fast when there are large no. of Records

The Union Operators Scans both the Table matches the record for uniqueness and returns distinct Records which slow down the execution time when records are very large in Numbers

,whereas Union All will fetch the Datawithout looking for distinct record.

Note: While Using Union or UnionALL no. of columns in the select statement should be Same Secondly the Column Names by default  will be the First query column Names.