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.

SELECTContactName,CompanyNameFROMCustomers

UNION ALL

SELECTFirstName +' '+ LastName,'Northwind'FROMEmployees

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

(SELECTContactName,CompanyNameFROMCustomers

UNIONALL

SELECTFirstName+' '+LastName,'Northwind'FROMEmployees)ASt

ORDERBYContactName

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.