T-SQL » Operators » UNION
- Syntax:
- query1 UNION [ ALL ] query2 [ UNION [ ALL ] queryn [ ... ] ]
- query1
- Is a SELECT statement that returns data that is to be combined with data from another query.
- UNION
- Is the keyword that specifies that multiple result sets are to be combined and returned as a single result set.
- ALL
- Is the keyword that indicates that all rows, including duplicate rows are to be combined into the result set.
The UNION operator is used to merge the results from any combination of two or more queries or SELECT statements into a single table.
All queries in a UNION must request the same number of fields, though they don't have to be of the same size or data type. By default, a UNION only returns unique records, but by using the ALL predicate you ensure that all records are returned. This also makes the query run faster.
Examples
Code:
SELECT Name, City FROM Suppliers
WHERE Country = 'Mexico'
UNION SELECT Name, City FROM Customers
WHERE Country = 'Mexico';
Output:
Name City Dell Mexico City Computer Wholesale Warehouse Guadalajara Computer Hardware Ltd. Cancun Jorge Lopez Nogales Rafael Sanchez Manzanillo Jose Cruz Mexico City Luis Castillo Cancun
(7 row(s) affected)
Explanation:
This example takes certain selected fields from the 'Customers' table and joins them onto the end of the same number of selected fields from the 'Suppliers' table.
Language(s): MS SQL Server
Code:
SELECT Name, City, 'Supplier' AS Source
FROM Suppliers
WHERE Country = 'Canada'
UNION SELECT Name, City, 'Customer'
FROM Customers
WHERE Country = 'Canada'
ORDER BY City, Source;
Output:
Name City Source Software and Peripherals Inc. Montreal Supplier Colin Graphton Toronto Customer Personal Computer Electronics Toronto Supplier Jacque Jones Winnipeg Customer Louie Franks Winnipeg Customer
(5 row(s) affected)
Explanation:
This example shows how by adding an ORDER BY clause at the end of the last query we can specify the order of the returned data.
Language(s): MS SQL Server
See Also:
