UNION / UNION ALL

UNION: Combines results and removes duplicates.

SELECT col1, col2 FROM table_a
UNION
SELECT col1, col2 FROM table_b;

UNION ALL: Combines results and keeps all rows, including duplicates.

SELECT col1, col2 FROM table_a
UNION ALL
SELECT col1, col2 FROM table_b;

You can chain three or more SELECT statements.

SELECT col1, col2 FROM table_a
UNION ALL
SELECT col1, col2 FROM table_b
UNION ALL
SELECT col1, col2 FROM table_c
ORDER BY col1;

Syntax	Description
UNION	Combines multiple SELECT results vertically and removes duplicate rows.
UNION ALL	Combines multiple SELECT results vertically and keeps all rows, including duplicates. Faster than UNION.

The following full_time_employees and contract_employees tables are used in the examples below. Shiina Mayuri works at both companies, so she appears in both tables.

Combine full-time and contract employee names into one list. UNION removes duplicates, so Shiina Mayuri appears only once.

sample_union.sql

SELECT name FROM full_time_employees
UNION
SELECT name FROM contract_employees
ORDER BY name;

+---------------+
| name          |
+---------------+
| Hashida Itaru |
| Makise Kurisu |
| Okabe Rintaro |
| Shiina Mayuri |
+---------------+
4 rows in set

UNION ALL keeps duplicates. Since Shiina Mayuri exists in both tables, she appears twice.

sample_union.sql

SELECT name FROM full_time_employees
UNION ALL
SELECT name FROM contract_employees
ORDER BY name;

+---------------+
| name          |
+---------------+
| Hashida Itaru |
| Makise Kurisu |
| Okabe Rintaro |
| Shiina Mayuri |
| Shiina Mayuri |
+---------------+
5 rows in set

Adding a constant value to each SELECT labels which table each row came from.

sample_union.sql

SELECT name, 'Full-time' AS employment_type
FROM full_time_employees
UNION ALL
SELECT name, 'Contract'
FROM contract_employees
ORDER BY name;

+---------------+-----------------+
| name          | employment_type |
+---------------+-----------------+
| Hashida Itaru | Contract        |
| Makise Kurisu | Full-time       |
| Okabe Rintaro | Full-time       |
| Shiina Mayuri | Full-time       |
| Shiina Mayuri | Contract        |
+---------------+-----------------+
5 rows in set

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

-- Works in MySQL, PostgreSQL, SQL Server, Oracle, and SQLite
SELECT name, 'Full-time' AS employment_type
FROM full_time_employees
UNION ALL
SELECT name, 'Contract'
FROM contract_employees
ORDER BY name;
-- Result: 5 rows (Shiina Mayuri appears twice, once per table)

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.