Difference between UNION and UNION ALL in SQL

In this article we will see the difference between UNION and UNION ALL Sql Keywords.

Syntax:

[SQL Statement 1]


UNION [ALL]


[SQL Statement 2]

Where ALL statement is optional.

Both the Sql statements must consist of equal number of fields.


Similarties:


The UNION and UNION ALL Sql Keywords are used to combine two or more sql queries and return the result set consisting of a single set of all queries mentioned in there.


Differences:

The main difference between UNION and UNION ALL is that UNION retrieves distinct values (With out duplicates) where as UNION ALL retrieves all values from the result set including duplicates.


Example

Consider Employees Table of NorthWind database.


UNION ALL

The following query is used to display the EmployeeId and lastName.

SELECT EmployeeID, lastName
FROM [dbo].[Employees]

UNION ALL

SELECT EmployeeID, lastName
FROM [dbo].Employees


RESULT


EmployeeId LastName

5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth
5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth

The repetition of same records twice. Thus duplicate records exists in this result set.

UNION

The following query is used to display the EmployeeId and lastName.

SELECT EmployeeID, lastName
FROM [dbo].[Employees]

UNION

SELECT EmployeeID, lastName
FROM [dbo].Employees


RESULT

EmployeeId LastName

5 Buchanan
8 Callahan
1 Davolio
9 Dodsworth


Here only distinct values are returned. Thus no duplicate records exists in this result set.


Same artcicle available underhttp://www.dotnetspider.com/resources/29704-Difference-between-UNION-UNION-ALL-SQL.aspx