What Is The Difference Between UNION And UNION ALL?

Hey Justus,

The key difference between Union and Union ALL is that the Union eliminates duplicate records where all the columns in the results are the same whereas,  Union all does not eliminate duplicate records rather it selects the rows from all the tables that meet the condition of a specific query and combine into a result table.

Select Column1, Column2, Column3 from Table A
UNION
Select Column1, Column2, Column3 from Table B

For example: Consider two tables.

Table1:

Table:2

SELECT EmpName FROM Employee
UNION
SELECT ManagerName FROM Manager

No.	EmpName
1	Arjun
2	Abhi
3	Chaitanya
4	David
5	Karthik
6	Sameer
7	Priya

Union ALL is a command used to concatenate the records. It pulls the values from all the tables so it does not remove the duplicate records.

Select Column1, Column2, Column3 from Table A
UNION ALL
Select Column1, Column2, Column3 from Table B

No	EmpName
1	Arjun
2	Chaitanya
3	David
4	Karthik
5	Priya
6	Abhi
7	David
8	Sameer
9	Priya

 

As we can see the Union ALL command doesn’t remove the duplicate records.

Union and Union ALL are the SQL operators used to concatenate two or more result sets.