SQL guide · Querying

SQL UNION: Combine Two Queries Into One Result

The UNION operator combines the results of two or more SELECT statements into a single result set and removes duplicate rows. Where a join places columns from different tables side by side, a union stacks rows on top of each other. It is the tool to reach for when similar data lives in several tables, for example current and archived orders, or customers and suppliers who both need a contact list.

The queries you combine must follow a few rules. Each must return the same number of columns, and the matching columns must have compatible data types. The column names of the result come from the first query, so alias columns there. Each SELECT can have its own FROM, WHERE and GROUP BY, but a single ORDER BY for the whole union goes after the last query and sorts the combined result.

Because UNION removes duplicates, the database has to compare rows, usually by sorting or hashing, and that costs time on large inputs. If you know the inputs cannot overlap, or you want to keep duplicates, use UNION ALL, which is faster and keeps every row. Two rows count as duplicates when all of their column values match, and NULL values are treated as equal for this purpose. Related set operators are INTERSECT, which returns rows found in both queries, and EXCEPT (MINUS in Oracle), which returns rows found only in the first.

Basic syntax

SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b
[ORDER BY column1];

Both SELECT statements must return the same number of columns in the same order with compatible types. Output column names are taken from the first query. A trailing ORDER BY applies to the whole combined result, and can refer to column names or positions from the first query.

SQL UNION examples

Combine two tables into one list

Customers and suppliers both have cities. UNION merges them into one list of unique city names, so a city that appears in both tables shows up once.

SELECT city FROM customers
UNION
SELECT city FROM suppliers
ORDER BY city;

Add a label column

When combining different sources, add a constant column to record where each row came from. The label also makes rows from different tables distinct from each other.

SELECT first_name AS name, email, 'customer' AS source
FROM customers
UNION
SELECT contact_name, email, 'supplier'
FROM suppliers;

Union with filters

Each branch can have its own conditions. This returns the ids of customers who either placed a large order this year or filed a complaint, without repeating anyone who did both.

SELECT customer_id FROM orders
WHERE total_amount > 1000 AND order_date >= '2026-01-01'
UNION
SELECT customer_id FROM complaints
WHERE created_at >= '2026-01-01';

Current and archived data

Large tables are often split by age. A union lets you query both parts as if they were one table, and wrapping it in a subquery lets you aggregate the combined rows.

SELECT YEAR(order_date) AS yr, COUNT(*) AS orders
FROM (
  SELECT order_id, order_date FROM orders
  UNION
  SELECT order_id, order_date FROM orders_archive
) AS all_orders
GROUP BY YEAR(order_date);

Where SQL dialects differ

PostgreSQL: Supports UNION, INTERSECT and EXCEPT with and without ALL. Column types are resolved pairwise, and PostgreSQL raises an error if they cannot be matched, such as integer with text; add a cast. Parenthesise branches that use their own ORDER BY or LIMIT.

MySQL: Supports UNION and UNION ALL. INTERSECT and EXCEPT arrived in MySQL 8.0.31. Parenthesise a branch to give it its own ORDER BY with LIMIT. Result column names come from the first query.

SQL Server: Supports UNION, INTERSECT and EXCEPT. Types are reconciled by data type precedence, which can cause implicit conversions or errors. A final ORDER BY must use column names or aliases of the first query.

SQLite: Supports UNION, UNION ALL, INTERSECT and EXCEPT. Parentheses around individual SELECT statements in a compound query are not allowed, so use a subquery if a branch needs its own LIMIT.

Oracle: Supports UNION and UNION ALL, and uses MINUS instead of EXCEPT (EXCEPT is accepted from Oracle 21c). Column types must match in group, for example both character types. An empty string is NULL, which influences duplicate removal.

Common mistakes

  • Using UNION when duplicates are impossible or wanted. The extra de-duplication step slows the query, and UNION ALL is usually the right choice.
  • Selecting a different number of columns in each branch, which fails with a column count error.
  • Putting ORDER BY after the first query. It must follow the last SELECT and applies to the whole result.
  • Mixing columns in a different order across branches. Union matches by position, not by name, so values may land in the wrong column silently.
  • Combining incompatible types such as dates and strings without casting, which causes errors or surprising implicit conversions.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator