UNION [ALL]

Concatenates the result sets of two queries into a single result set. In order to union the result sets, the two queries must:

·         Return the same number of columns, in the same order

·         The data types for the columns at the same position in the two results sets must be the same

When using UNION ALL the result set is allowed to contain duplicate rows, so it will contain all rows from both sets. When using UNION duplicate rows will be removed. The following query returns the union of customers and employees.

SELECT ContactName, CompanyName FROM Customers

UNION ALL

SELECT FirstName + ' ' + LastName, 'Northwind' FROM Employees

The order of the rows in the output is generally unspecified. To apply a specific order, the ORDER BY keyword can be used outside the union. The following query returns the union of customers and employees, ordered by ContactName.

SELECT * FROM

(SELECT ContactName, CompanyName FROM Customers

UNION ALL

SELECT FirstName + ' ' + LastName, 'Northwind' FROM Employees) AS t

ORDER BY ContactName

Note: to order the result of a UNION operation it is necessary to enclose the UNION in a subquery and apply ORDER BY to the result of the subquery.