SQL guide · Querying

SQL UNION ALL: Stack Query Results Without Removing Duplicates

The UNION ALL operator joins the results of two or more SELECT statements into one result set and keeps every row, including duplicates. It simply appends the second result to the first. Because it skips the work of finding and removing duplicates, it is faster and uses less memory than UNION, and on large inputs the difference can be significant.

The same compatibility rules apply as for UNION: every query must return the same number of columns, and the column in each position must have a compatible type. Names come from the first query. Use UNION ALL when the inputs are already distinct, as when you combine monthly tables or partitions, when duplicates are meaningful and must be counted, or when you need to add a source label to each branch and keep all rows.

A common pattern is to build a combined data set with UNION ALL and then aggregate over it with a wrapper query, for example to total sales across several regional tables. It is also the standard way to unpivot a wide table into a long one, by selecting each measure column as its own branch. When you are unsure which operator to use, ask whether a repeated row should appear twice in the result. If the answer is yes, or the data cannot repeat anyway, choose UNION ALL, and only add de-duplication when you know you need it.

Basic syntax

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

Each branch must return the same number of columns with compatible types, matched by position. You can chain as many branches as you like. ORDER BY goes once at the very end and sorts the entire combined result.

SQL UNION ALL examples

Keep duplicates from two tables

Unlike UNION, this returns every city from both tables, so a city shared by a customer and a supplier appears twice. Use it when you want to count occurrences.

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

Combine regional tables and total them

Sales are stored in one table per region. UNION ALL builds a single data set, and the outer query adds up revenue per product without losing any rows.

SELECT product_id, SUM(amount) AS revenue
FROM (
  SELECT product_id, amount FROM sales_north
  UNION ALL
  SELECT product_id, amount FROM sales_south
  UNION ALL
  SELECT product_id, amount FROM sales_west
) AS all_sales
GROUP BY product_id;

Add a source column

A literal column records which table each row came from. This is useful for audits and for filtering the combined set later.

SELECT order_id, order_date, total_amount, 'current' AS source
FROM orders
UNION ALL
SELECT order_id, order_date, total_amount, 'archive'
FROM orders_archive
ORDER BY order_date DESC;

Unpivot columns into rows

Each branch selects one measure column and labels it. The result has one row per metric, a shape that is easier to chart and filter than the wide original.

SELECT store_id, 'q1' AS quarter, q1_sales AS sales FROM store_sales
UNION ALL
SELECT store_id, 'q2', q2_sales FROM store_sales
UNION ALL
SELECT store_id, 'q3', q3_sales FROM store_sales
UNION ALL
SELECT store_id, 'q4', q4_sales FROM store_sales;

Where SQL dialects differ

PostgreSQL: Supports UNION ALL, INTERSECT ALL and EXCEPT ALL. Branch column types are resolved pairwise and mismatches raise an error unless cast. UNION ALL is cheap because no sort or hash is needed.

MySQL: Supports UNION ALL in all versions, but INTERSECT and EXCEPT only from 8.0.31. Use parentheses around a branch to apply ORDER BY with LIMIT to that branch alone.

SQL Server: Supports UNION ALL. The result type of each column follows data type precedence, so mixing int and varchar can raise a conversion error. INTERSECT and EXCEPT exist but have no ALL variants.

SQLite: Supports UNION ALL in compound SELECT statements. Individual branches cannot be wrapped in parentheses, so use a subquery to give a branch its own limit.

Oracle: Supports UNION ALL, and also INTERSECT and MINUS without an ALL option. Oracle UNION ALL does not sort, but a bare UNION does sort implicitly in many plans, so do not rely on ordering of either.

Common mistakes

  • Choosing UNION ALL when you actually need unique rows, which leaves duplicates in reports that people expect to be clean.
  • Relying on the order in which rows appear. Even though branches are usually returned in sequence, only a final ORDER BY guarantees it.
  • Mismatched column order between branches. Values are matched by position, so a swapped column silently mixes data.
  • Omitting a source label when branches can contain the same key, which makes it impossible to tell which table a row came from.
  • Using UNION ALL to join related tables that should be combined with a join. A union adds rows, a join adds columns.

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