A union query performs the ‘union‘ set operation on two or more tables.  The union operation returns all tuples from all tables (like appending a second table to the bottom of the first). The union operation also allows you to sort the resulting data, perform where restriction, etc.  The syntax for the UNION operator is shown below.

Format

SELECT fields FROM tables WHERE criteria GROUP BY field HAVING criteria
UNION
SELECT fields FROM tables WHERE criteria GROUP BY field HAVING criteria ORDER BY sortcriteria;

Each select is a standard select with two exceptions.  First, the fields shown in the SELECT clause must be ‘union compatible’ (i.e., equivalent number, type, and order).  Second, there can only be one order by for the entire query.

Example 1

Join a compatible customer table and a supplier table for all customers and suppliers located in ‘Brazil’.  Sort the final result by zip code.
SELECT CompanyName, City, Zip, Region, SupplierID AS ID FROM Suppliers WHERE Country = ‘Brazil’ UNION SELECT CompanyName, City, Zip, Region, CustomerID AS ID FROM Customer WHERE Country = ‘Brazil’ ORDER BY Zip;

Oracle SQL also supports an INTERSECTION and MINUS clause that is similar to the UNION clause.  These two new connectors have the same syntax as the UNION clause and perform the set operations indicated by their name.