SQL UNION

SQL UNION operator is used to combine the results of two or more queries into one result. Duplicate rows are removed from the combined results when using the SQL UNION operator and the combined results have distinct values for each row. If you are certain that no duplicate rows exist in individual results, or you want to keep duplicate rows, use the SQL UNION ALL keywords. SQL UNION ALL keeps the rows from individual result sets and is faster than the UNION operator.

There are restrictions on results that can be combined by a SQL UNION operator:

• The two results need to contain the same number of columns.
• The corresponding items in each result must have similar data types, not the same column names. The column name of the first result becomes the column name of the combined result.

You can use an ORDER BY clause following the last SELECT clause and refer to the ordered column by its position in the SELECT list column number.

Example:
The following query list all the products where the price of the product exceeds $2,000 or where more than $30,000 of the product has been ordered in a single order.

SELECT mfr_id, product_id
FROM products
WHERE price > 2000
UNION
SELECT mfr_id, product
FROM orders
WHERE amount > 30000;

UNION ALL
UNION ALL is SQL UNION without the duplicate elimination.

Example:
The following query is same as above without elimination of duplicate column. It also sort results by mfr_id

SELECT mfr_id, product_id
FROM products
WHERE price > 2000
UNION
SELECT mfr_id, product
FROM orders
WHERE amount > 30000
ORDER BY mfr_id;