JetSQL » Operators » UNION

TABLE] query1
[TABLE] query2
[TABLE] queryn [ ... ]]
[GROUP BY grouplist, [...]]
Is a SELECT statement that returns data that is to be combined with data from another query.
Is the keyword that specifies that multiple result sets are to be combined and returned as a single result set.
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.


SELECT Name, City FROM Suppliers
WHERE Country = 'Mexico'
UNION SELECT Name, City FROM Customers
WHERE Country = 'Mexico';
DellMexico City
Computer Wholesale WarehouseGuadalajara
Computer Hardware Ltd.Cancun
Jorge LopezNogales
Rafael SanchezManzanillo
Jose CruzMexico City
Luis CastilloCancun

(7 row(s) affected)

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
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;
Software and Peripherals Inc.MontrealSupplier
Colin GraphtonTorontoCustomer
Personal Computer ElectronicsTorontoSupplier
Jacque JonesWinnipegCustomer
Louie FranksWinnipegCustomer

(5 row(s) affected)

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: