UNION / UNION ALL

-- UNION: Combines results and removes duplicates.
SELECT col1, col2 FROM tableA
UNION
SELECT col1, col2 FROM tableB;

-- UNION ALL: Combines results and keeps all rows, including duplicates.
SELECT col1, col2 FROM tableA
UNION ALL
SELECT col1, col2 FROM tableB;

-- You can chain three or more SELECT statements.
SELECT col1, col2 FROM tableA
UNION ALL
SELECT col1, col2 FROM tableB
UNION ALL
SELECT col1, col2 FROM tableC
ORDER BY col1;

-- Combine full-time and contract employees into one list (duplicates removed).
SELECT emp_name, 'Full-time' AS employment_type
FROM full_time_employees
UNION
SELECT emp_name, 'Contract'
FROM contract_employees
ORDER BY emp_name;

-- Combine this month's and last month's orders, keeping all rows for full aggregation.
SELECT customer_id, order_date, total_amount
FROM orders_this_month
UNION ALL
SELECT customer_id, order_date, total_amount
FROM orders_last_month
ORDER BY order_date DESC;

-- Add a constant value to output rows with a region label.
SELECT 'Tokyo' AS region, store_name, sales
FROM stores
WHERE prefecture = 'Tokyo'
UNION ALL
SELECT 'Osaka', store_name, sales
FROM stores
WHERE prefecture = 'Osaka'
ORDER BY sales DESC;

-- Full-time and contract employee list (UNION removes duplicates).
emp_name       | employment_type
---------------+----------------
Hanako Suzuki  | Full-time
Jiro Sato      | Contract
Megumi Tanaka  | Full-time
Taro Yamada    | Contract

-- Store list by region.
region | store_name    | sales
-------+---------------+---------
Tokyo  | Akihabara     | 3200000
Osaka  | Namba         | 2850000
Tokyo  | Shinjuku      | 2600000

The UNION and UNION ALL syntax is supported consistently across major databases.

-- Works in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT emp_name, 'Full-time' AS employment_type
FROM full_time_employees
UNION ALL
SELECT emp_name, 'Contract'
FROM contract_employees
ORDER BY emp_name;

In Oracle, be careful with string literal type conversion. Column data types must be strictly compatible, so use CAST to align types where necessary.

To use UNION, all SELECT statements must have the same number of columns, and the corresponding columns must have compatible data types. The column names from the first SELECT statement are used in the result.

If you are certain there are no duplicates, or if you want to keep duplicates, use UNION ALL. UNION performs a sort operation to remove duplicates, making it slower than UNION ALL. The difference becomes more noticeable with large numbers of rows.

ORDER BY is written once at the end of the entire statement. You cannot add ORDER BY to individual SELECT statements. For operators that extract common or differing rows, see INTERSECT / EXCEPT.