SQL UNION ALL

What is SQL UNION ALL and how does it work?

Combines result sets of two or more SELECT statements and returns all rows, including duplicates, in a single result.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL UNION ALL: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, Snowflake, BigQuery, Redshift, SQLite, DB2

SQL UNION ALL Full Explanation

SQL UNION ALL appends the output of multiple SELECT statements into one combined result set without removing duplicate rows. Each SELECT must return the same number of columns in the same order and with compatible data types. Because UNION ALL skips the overhead of duplicate elimination performed by UNION (which implies DISTINCT), it is generally faster and preserves every occurrence of each row. ORDER BY can be applied only once, after the final SELECT. LIMIT, OFFSET, or FETCH clauses must also appear at the end, not inside individual SELECT blocks. NULLability follows normal type-promotion rules; column names in the final set default to those in the first SELECT unless aliased. Beware of unintended data explosions when duplicates are expected.

SQL UNION ALL Syntax

SELECT column_list
FROM table1
UNION ALL
SELECT column_list
FROM table2;

SQL UNION ALL Parameters

Example Queries Using SQL UNION ALL

-- Combine sales records from two years, keeping duplicates
SELECT order_id, customer_id, total_amount
FROM sales_2023
UNION ALL
SELECT order_id, customer_id, total_amount
FROM sales_2024;

-- Merge two tables with different sources and sort the full list
SELECT user_id, event_date
FROM web_events
UNION ALL
SELECT user_id, event_date
FROM mobile_events
ORDER BY event_date DESC;

Expected Output Using SQL UNION ALL

The queries return a single result set that lists rows from all SELECT statements in the order the database engine processes them (or in the ORDER BY order if specified)
Duplicate rows remain visible

Use Cases with SQL UNION ALL

Append monthly or yearly partitioned tables while preserving duplicate rows for auditing
Combine log sources (web, mobile, API) into one stream without deduplication
Stack staging data under production data before running summary aggregation
Improve performance where DISTINCT elimination of UNION is unnecessary

Common Mistakes with SQL UNION ALL

Assuming UNION ALL removes duplicates (it does not)
Placing ORDER BY inside each SELECT instead of after the last UNION ALL
Mismatching column counts or incompatible data types between SELECT statements
Expecting column aliases from later SELECTs to propagate to the final result

First Introduced In

SQL-92 standard

Frequently Asked Questions

What is the main advantage of UNION ALL?

It skips duplicate elimination, making queries faster and preserving every row from each SELECT.

How do I sort the combined result set?

Place an ORDER BY clause after the last SELECT in the UNION ALL chain. Sorting inside individual SELECTs is ignored.

Can the SELECT statements have different column names?

Yes, but they must return the same number of columns in the same order and with compatible types. Final column names default to those in the first SELECT unless aliases are supplied there.

When should I choose UNION instead of UNION ALL?

Choose UNION when you need duplicate rows removed from the combined result. Otherwise, prefer UNION ALL for speed.