Using UNION operator

You can combine the results of several queries with UNION operator. By default, duplicate rows will be removed. To maintain a duplicate, then use the keyword ALL. Some rules of the UNION operator :

• Queries will be merged, must have many same column,same order, and similar data types.
• If you use INTO clause in one query, you should put that query as first query.
• You can use GROUP BY and HAVING clause in individual query.
• ORDER BY clause and compute, are permitted only at the end of the UNION operator, to define the sequence of final results or to calculate the final value.

 For example we will display all of the customers and suppliers from the USA, sorted by CompanyName:

SELECT CompanyName, ContactName FROM
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

If you want to save the results combined into a temporary table, you must use the INTO clause followed by table name, for example TempUSA

SELECT CompanyName, ContactName FROM
INTO TempUSA
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName