UNION ALL in SQL
Introduction
Modern applications frequently divide similar records among tables, partitions, regions, services, or historical archives. A sales dashboard may need domestic and international orders, an operations report may need current and archived events, and a testing platform may need API and UI executions. These sources represent the same kind of row, but every occurrence may matter. SQL provides UNION ALL to combine them without discarding repeated values.
UNION ALL appends the rows returned by two or more compatible SELECT statements. Unlike UNION, it does not perform duplicate elimination. If three branches return the same row, the result contains that row three times. This behavior is essential for transactions, events, logs, test runs, inventory movements, and other facts for which equal values do not imply the same occurrence.
This tutorial explains syntax and compatibility rules, then develops practical patterns for filtering, ordering, source labeling, aggregation, common table expressions, pagination, current-plus-history reporting, ETL, and performance analysis. It also shows how to prevent unintended duplication, preserve lineage, validate row grain, and choose correctly between UNION ALL, UNION, joins, and other set operators.
What Is UNION ALL in SQL?
UNION ALL is a set operator that stacks the result of each participating query into one result set while preserving every returned row. Columns are aligned by ordinal position. The first column from every branch feeds the first output column, the second column feeds the second output column, and so on.
SELECT column1, column2
FROM first_table
UNION ALL
SELECT column1, column2
FROM second_table;
Participating tables do not need a declared relationship. They only need queries that produce compatible output shapes. A branch can read a table, join several tables, aggregate data, use a subquery, or return constants. The result column names normally come from the first SELECT.
How UNION ALL Works
Conceptually, the database evaluates each branch, obtains its rows, and appends those rows to one stream. No global comparison is required to determine whether complete rows repeat. The optimizer can execute branches sequentially or in parallel and may apply many physical transformations, but the visible semantics remain an append of all results.
First SELECT -> Alice, Bob, Carol
Second SELECT -> Carol, David, Emma
UNION ALL result:
Alice, Bob, Carol, Carol, David, Emma
Carol remains twice because both source rows survive. The operator does not indicate whether those rows refer to one person duplicated across sources or two distinct people with the same name. Identity depends on the columns and business model chosen by the query.
Why Preserving Duplicates Matters
Repeated values are not always duplicate facts. Two customers can place equal orders, an API can return the same status many times, and a test can execute repeatedly with the same duration. Eliminating those rows changes totals, averages, counts, trends, and audit history. UNION ALL preserves the evidence needed for accurate analysis.
Even when sources should be disjoint, preserving rows can help detect a broken assumption. If current and archive tables overlap because a retention job failed, UNION ALL exposes the overlap. A broad UNION could conceal it, and only when every selected value matches.
Sample Data
The examples use regional employee tables, sales channels, and order partitions. Employee sources contain employee_id, employee_name, and department. Customer sources contain identifiers, names, and cities. Order sources contain order ID, customer ID, date, amount, and currency.
| Source | Purpose | Expected grain |
|---|---|---|
employees_us | US employee records | One row per employee |
employees_india | India employee records | One row per employee |
domestic_orders | Domestic transactions | One row per order |
international_orders | International transactions | One row per order |
current_events | Active operational events | One row per event |
archived_events | Historical events | One row per event |
Before combining any sources, state the grain in plain language. Every branch should represent the same logical unit. Stacking one-row-per-order detail with one-row-per-customer summaries produces a confusing relation even when the column counts happen to match.
Basic UNION ALL Example
The following query returns every employee name from both regional tables:
SELECT employee_name
FROM employees_us
UNION ALL
SELECT employee_name
FROM employees_india;
If Carol appears in each table, both rows remain. Selecting names alone does not preserve reliable identity, so a production list should usually include the employee identifier and source region.
SELECT employee_id, employee_name, 'US' AS region
FROM employees_us
UNION ALL
SELECT employee_id, employee_name, 'INDIA' AS region
FROM employees_india;
UNION ALL vs UNION
The defining difference is duplicate processing. UNION returns distinct complete rows, whereas UNION ALL returns every row. Removing duplicates generally requires sorting or hashing, so UNION ALL is often less expensive. Correctness still comes first.
| Behavior | UNION ALL | UNION |
|---|---|---|
| Repeated rows | Preserved | Removed |
| Global distinct stage | No | Usually |
| Typical workload | Events and transactions | Unique reference lists |
| Main risk | Unintended double counting | Loss of valid repeated facts |
Do not select an operator only because one is faster. First decide whether two equal output rows represent one logical fact or two occurrences. Then choose the operator that preserves that meaning.
Rule One: Return the Same Number of Columns
Every branch must return the same number of columns. The following statement is invalid because one branch has two outputs and the other has three:
SELECT employee_id, employee_name
FROM employees_us
UNION ALL
SELECT employee_id, employee_name, department
FROM employees_india;
If a source lacks a field in the common model, provide a meaningful placeholder. Cast nulls where the engine cannot infer an appropriate common type.
SELECT employee_id, employee_name, department
FROM employees_us
UNION ALL
SELECT contractor_id, contractor_name,
CAST(NULL AS VARCHAR(50))
FROM contractors;
Rule Two: Use Compatible Data Types
Corresponding columns need types that the database can reconcile. Product rules for type precedence and implicit conversion differ. Combining an integer identifier with text may convert one side, fail, or produce surprising values.
SELECT CAST(order_id AS VARCHAR(40)) AS record_id,
total_amount
FROM domestic_orders
UNION ALL
SELECT partner_reference,
total_amount
FROM partner_orders;
Explicit casts document the common contract. Choose sufficient length and precision, and normalize date, timestamp, currency, and character collation differences deliberately.
Rule Three: Column Positions Must Match
SQL does not align branches by alias. It aligns by position. A branch that reverses customer name and city may still execute because both fields are text, but it corrupts the meaning of the final result.
SELECT customer_id, customer_name, city
FROM online_customers
UNION ALL
SELECT customer_id, customer_name, city
FROM store_customers;
Keep a visible common projection order and avoid SELECT *. Schema evolution can otherwise reorder or add source columns and silently break a long-lived union view.
Rule Four: The First Query Defines Output Names
The first branch normally supplies final aliases. Later aliases do not rename the combined columns.
SELECT employee_id AS person_id,
employee_name AS person_name
FROM employees
UNION ALL
SELECT contractor_id,
contractor_name
FROM contractors;
The combined columns are person_id and person_name. Make the first branch descriptive because downstream queries, exports, and applications depend on that contract.
Rule Five: Row Order Is Not Guaranteed
The database may read branches or partitions in different orders depending on plans, parallelism, indexes, and runtime conditions. The apparent source order is not a presentation guarantee. Use one final ORDER BY.
SELECT employee_id, employee_name, department
FROM employees_us
UNION ALL
SELECT employee_id, employee_name, department
FROM employees_india
ORDER BY department, employee_name, employee_id;
Include a unique tiebreaker when deterministic sequence matters. Ordering by department and name alone leaves employees with equal values free to exchange positions.
Sorting with Output Aliases
A final alias from the first branch can usually be used to order the complete result.
SELECT order_id,
order_date AS activity_time,
total_amount
FROM domestic_orders
UNION ALL
SELECT order_id,
order_date,
total_amount
FROM international_orders
ORDER BY activity_time DESC, order_id DESC;
Some database products impose specific syntax restrictions around expressions and branch-level ordering. A derived table provides a portable boundary when the final operation becomes complex.
Filtering Each Branch
Each query can apply its own WHERE clause. Early filtering reduces data read and transferred to the append stage.
SELECT employee_id, employee_name, department, 'US' AS region
FROM employees_us
WHERE department = 'IT'
UNION ALL
SELECT employee_id, employee_name, department, 'INDIA'
FROM employees_india
WHERE department = 'IT';
Conditions may differ when sources use different lifecycle rules. Document why the branches differ so future maintainers do not “standardize” intentional logic.
Filtering the Final Combined Result
Wrap the union in a derived table or CTE when one condition should apply uniformly after all rows have been normalized.
SELECT order_id, customer_id, total_amount, source
FROM (
SELECT order_id, customer_id, total_amount,
'DOMESTIC' AS source
FROM domestic_orders
UNION ALL
SELECT order_id, customer_id, total_amount,
'INTERNATIONAL'
FROM international_orders
) AS all_orders
WHERE total_amount >= 1000;
Optimizers may push the outer filter into branches when safe. Express the clearest logic first and verify the actual plan when performance matters.
Combining Three or More Sources
UNION ALL can connect many queries. Compatibility rules apply to every branch.
SELECT event_id, occurred_at, event_type, 'US' AS region
FROM events_us
UNION ALL
SELECT event_id, occurred_at, event_type, 'EU'
FROM events_eu
UNION ALL
SELECT event_id, occurred_at, event_type, 'APAC'
FROM events_apac;
As branch count grows, repeated transformations are a maintenance risk. Consider normalized staging views, generated pipelines, table partitioning, or a warehouse fact model rather than manually duplicating a large projection.
Adding Source Identity
Local keys may overlap across systems. Adding a source column makes lineage explicit and creates a composite business identity.
SELECT 'CRM' AS source_system,
customer_id AS source_customer_id,
customer_name
FROM crm_customers
UNION ALL
SELECT 'ERP', account_id, account_name
FROM erp_accounts;
The pair (source_system, source_customer_id) is safer than the local identifier alone. Keep this lineage through staging, reconciliation, error handling, and audit reports.
Combining Aggregate Results
Branches can produce regional summaries that are stacked into one report.
SELECT 'DOMESTIC' AS market,
COUNT(*) AS order_count,
SUM(total_amount) AS sales_amount
FROM domestic_orders
UNION ALL
SELECT 'INTERNATIONAL',
COUNT(*),
SUM(total_amount)
FROM international_orders;
The result has one summary row per source. It does not provide a grand total unless another query aggregates those summaries or the detail rows.
Calculating a Grand Total Safely
To calculate totals across all sources, combine detail or compatible partial aggregates and aggregate outside.
SELECT SUM(total_amount) AS grand_total
FROM (
SELECT total_amount
FROM domestic_orders
UNION ALL
SELECT total_amount
FROM international_orders
) AS combined_orders;
UNION ALL is crucial because two equal amounts can belong to different orders. UNION could remove a legitimate amount when no transaction identity is selected. If branches overlap by record, correct the overlap using business keys rather than distinct amount values.
Combining Detail and Summary Rows
Reports sometimes append a labeled total row to detail records. Every branch must have the same columns and compatible types.
SELECT CAST(order_id AS VARCHAR(30)) AS row_label,
total_amount,
0 AS sort_group
FROM domestic_orders
UNION ALL
SELECT 'GRAND TOTAL',
SUM(total_amount),
1
FROM domestic_orders
ORDER BY sort_group, row_label;
A dedicated sort column keeps the total at the bottom without relying on label text. Reporting tools may handle totals more cleanly than SQL, so choose the layer that owns presentation.
NULL Values and Missing Columns
UNION ALL preserves every null-containing row. If both branches return a null city five times in total, all five rows remain. A null placeholder can also represent a field unavailable from one source.
SELECT customer_id, customer_name, loyalty_level
FROM online_customers
UNION ALL
SELECT customer_id, customer_name,
CAST(NULL AS VARCHAR(20))
FROM guest_customers;
Do not use null to hide a modeling mismatch. Confirm that the field truly does not apply or is unavailable, and distinguish unknown from not applicable where the business requires that difference.
Combining Results from Different JOINs
Each branch can contain its own joins as long as the final projections align.
SELECT o.order_id, c.customer_name,
o.total_amount, 'DOMESTIC' AS source
FROM domestic_orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
UNION ALL
SELECT o.order_id, c.customer_name,
o.total_amount, 'INTERNATIONAL'
FROM international_orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id;
Validate branch cardinality independently. An unintended one-to-many join can multiply orders before the union, and UNION ALL correctly preserves that multiplication even when it is a query defect.
UNION ALL vs JOIN
UNION ALL adds rows vertically. A join adds columns horizontally by matching related records. Use a union when branches represent the same row type and a join when attributes from related entities belong on one row.
| Design question | UNION ALL | JOIN |
|---|---|---|
| Need more compatible rows? | Yes | No |
| Need related columns? | No | Yes |
| Requires matching condition? | No | Usually |
| Preserves repeated branch rows? | Yes | Depends on cardinality |
Using UNION ALL in a CTE
A common table expression gives the complete stream a useful name and separates source normalization from later analysis.
WITH all_sales AS (
SELECT order_id, customer_id, order_date,
total_amount, 'DOMESTIC' AS market
FROM domestic_orders
UNION ALL
SELECT order_id, customer_id, order_date,
total_amount, 'INTERNATIONAL'
FROM international_orders
)
SELECT market, COUNT(*) AS orders,
SUM(total_amount) AS sales
FROM all_sales
GROUP BY market;
A CTE improves readability but does not guarantee materialization or faster execution. Database engines can inline or materialize it according to their rules and estimates.
Using UNION ALL in a Subquery
A derived table is convenient for one downstream operation.
SELECT customer_id, COUNT(*) AS order_count
FROM (
SELECT customer_id FROM domestic_orders
UNION ALL
SELECT customer_id FROM international_orders
) AS order_customers
GROUP BY customer_id
HAVING COUNT(*) > 1;
This counts every order occurrence across both sources. If the intended measure is distinct orders, include a globally unique order key and define how cross-source collisions should be handled.
Limiting the Combined Result
Apply a final limit after a deterministic order when the requirement asks for the top rows across all sources.
SELECT order_id, order_date, total_amount, source
FROM (
SELECT order_id, order_date, total_amount,
'DOMESTIC' AS source
FROM domestic_orders
UNION ALL
SELECT order_id, order_date, total_amount,
'INTERNATIONAL'
FROM international_orders
) AS all_orders
ORDER BY order_date DESC, source, order_id DESC
FETCH FIRST 20 ROWS ONLY;
Applying a limit inside every branch answers a different question: top rows per source before combining. Parentheses and database-specific syntax may be required. Never assume a branch limit without ordering selects stable records.
Pagination and Stable Ordering
Combined feeds need a total ordering. A timestamp alone may tie, and local identifiers may collide. Include source and a unique key as tiebreakers.
ORDER BY activity_time DESC,
source_system,
source_record_id DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;
Offset pagination becomes expensive at deep pages and can shift when new rows arrive. Keyset pagination uses the last seen ordering values and is often more stable for event feeds. The cursor must include every column in the ordering tuple.
Why UNION ALL Is Often Faster
UNION ALL generally avoids the global sort or hash operation required to remove duplicates. The database can frequently concatenate branch outputs and stream rows sooner. It may also execute independent branches in parallel.
This does not make every union-all query fast. Branch scans, joins, conversions, network exchanges, outer sorting, and downstream aggregation can dominate cost. Performance claims should come from actual plans and representative workloads.
Reading the Execution Plan
Look for append, concatenation, or union-all operators and inspect each child branch. Confirm that predicates are pushed down, expected partitions are pruned, indexes support filters and joins, and row estimates resemble reality. Check whether a final sort spills to temporary storage.
Compare the plan with an equivalent UNION query. The distinct version often adds a sort, hash aggregate, or unique stage. Measure elapsed time, CPU, reads, memory, temp space, and rows rather than judging only operator cost percentages.
Performance Best Practices
Select only required columns, push safe filters into branches, align data types to avoid runtime conversions, and use UNION ALL when complete preservation is the requirement. Index branch filter and join keys based on real access patterns. For large date-partitioned tables, constrain dates so the engine can skip irrelevant partitions.
If the same large combination powers many reports, consider a partitioned table, normalized view, materialized view, staging table, or warehouse fact table. Repeating dozens of branches in application SQL increases parse time and maintenance risk.
Current and Historical Data
A common pattern combines active records with an archive:
SELECT event_id, occurred_at, event_type, payload,
'CURRENT' AS storage_tier
FROM current_events
UNION ALL
SELECT event_id, occurred_at, event_type, payload,
'ARCHIVE'
FROM archived_events;
The design assumes the tables do not overlap. Test that assumption using the event key. If retention movement is asynchronous, a temporary overlap or gap may occur; reporting logic needs a documented consistency model rather than accidental deduplication.
Regional Sales Consolidation
Regional systems may reuse order numbers, currencies, and local time zones. A global feed should carry region, normalize timestamps to a standard zone, and either convert currency or retain currency code.
SELECT 'US' AS region, order_id,
order_time_utc, amount, currency_code
FROM sales_us
UNION ALL
SELECT 'EU', order_id,
order_time_utc, amount, currency_code
FROM sales_eu;
Summing amount across currencies is invalid until conversion uses an agreed exchange rate and effective time. SQL compatibility does not guarantee business comparability.
Application Logs and API Monitoring
Logs and request observations are occurrence data. Equal messages, status codes, or durations must remain separate because counts and timelines depend on every event.
SELECT request_id, observed_at, endpoint,
status_code, duration_ms, 'EAST' AS region
FROM api_logs_east
UNION ALL
SELECT request_id, observed_at, endpoint,
status_code, duration_ms, 'WEST'
FROM api_logs_west;
After combining, calculate error rates and latency percentiles in an outer query or analytics system. Ensure request identifiers are globally unique or pair them with region and service.
Employees and Contractors
A workforce directory may expose a common person model while preserving employment type.
SELECT employee_id AS person_id,
employee_name AS person_name,
department, 'EMPLOYEE' AS person_type
FROM employees
UNION ALL
SELECT contractor_id, contractor_name,
assigned_department, 'CONTRACTOR'
FROM contractors;
Do not assume local IDs are unique across entity types. Use the source type with the local ID, and keep role-specific authorization outside the reporting projection.
ETL and Data Warehousing
ETL pipelines frequently union batches from files, operational databases, and APIs into staging. UNION ALL preserves raw evidence for validation and reconciliation. A later quality step can identify duplicates using source keys, ingestion batch IDs, timestamps, and explicit survivorship rules.
This separation is important. Ingestion should not silently erase data simply because selected values match. Deduplication belongs to a documented transformation that records why a row was considered duplicate and which record survived.
UNION ALL vs Other Operators
UNION ALL returns every row from either input. UNION returns distinct rows from either input. INTERSECT returns rows common to inputs, while EXCEPT or MINUS returns rows from the first input that are absent from the second. Product support for all variants differs.
OR combines predicates in one query. It can overlap conditions, but one base row is ordinarily returned once by a single query block unless joins multiply it. Separate union-all branches can return the same base row multiple times when it satisfies multiple branch conditions. That difference can be intentional or a defect.
Defining Global Row Identity
Before combining independent systems, determine what makes one occurrence globally identifiable. A local order number may be unique inside a region but reused elsewhere. In that design, order_id alone is not a global key; the pair of region and order ID is. The union should carry both columns so downstream consumers can distinguish valid records.
SELECT 'US' AS source_region,
order_id AS source_order_id,
order_date, total_amount
FROM orders_us
UNION ALL
SELECT 'EU', order_id,
order_date, total_amount
FROM orders_eu;
Identity becomes more difficult when systems copy records between one another. A partner order imported into the main platform may appear under a new local ID while still representing the same business event. Source labels alone cannot resolve that relationship. The data model needs an external reference, correlation ID, or cross-reference table that records equivalence.
Write the overlap policy before aggregation. Sources may be strictly disjoint, temporarily overlapping during transfer, or permanently overlapping because one system mirrors another. Each case requires different handling. Disjoint sources can be appended directly. Temporary overlap needs consistency rules around the transfer window. Mirrored data needs explicit source precedence or canonical identity.
Avoid deduplicating with whichever columns happen to be visible in a report. Two legitimate orders can share customer, date, and amount, while two copies of one order may differ in update timestamp or formatting. Deduplication must use stable business identity and a documented rule for selecting or merging versions.
Detecting Unintended Overlap
When branches are supposed to be mutually exclusive, validate the assumption by grouping on the business key after combining them.
WITH combined AS (
SELECT order_id, 'CURRENT' AS source
FROM current_orders
UNION ALL
SELECT order_id, 'ARCHIVE'
FROM archived_orders
)
SELECT order_id, COUNT(*) AS occurrences
FROM combined
GROUP BY order_id
HAVING COUNT(*) > 1;
If order IDs are only locally unique, group by the complete key. This validation distinguishes legitimate repeated values from a broken source boundary.
Common Mistakes
- Using different column counts in participating queries.
- Aligning columns by name instead of position.
- Relying on unsafe implicit type conversion.
- Assuming output rows arrive in branch order.
- Combining different row grains because their shapes happen to match.
- Double counting records from overlapping current and archive tables.
- Dropping source identity when local keys can collide.
- Using
SELECT *in a long-lived union view. - Summing different currencies, units, or time definitions.
- Applying limits before ordering or at the wrong query level.
- Using
UNION ALLwhen the requirement is a unique reference list. - Replacing explicit data-quality resolution with accidental duplication.
Testing Checklist
Test empty branches, one-row branches, exact repeated rows, partial matches, null values, type boundaries, identifier collisions, overlapping filters, current/archive overlap, and equal timestamps. Assert the expected row count as well as values. Validate each branch separately before testing the combined relation.
For totals, construct two distinct transactions with equal amounts and confirm both contribute. For pagination, insert tied timestamps and confirm stable tiebreakers. For source lineage, verify every result can be traced back to a system and local key.
Recommended Guidelines
- Define a common row grain before combining sources.
- Use
UNION ALLonly when all occurrences should remain. - List columns explicitly in a consistent order.
- Normalize data types, units, time zones, and status values.
- Add source identity when local keys can overlap.
- Push selective predicates into branches where safe.
- Use one deterministic final ordering for presentation.
- Apply limiting and pagination at the correct logical level.
- Aggregate after combining when metrics span all sources.
- Test overlap assumptions rather than hiding violations.
- Inspect actual execution plans with realistic data.
- Use CTEs, views, or partitioning to manage repeated complexity.
Interview Questions and Answers
What is UNION ALL in SQL?
It is a set operator that appends compatible query results and preserves every returned row, including complete duplicates.
What happens when branches return identical rows?
Every occurrence remains. Two identical rows from two branches produce two output rows.
Why is UNION ALL usually faster than UNION?
It normally avoids the global sort or hash work needed to eliminate duplicates.
What rules apply to UNION ALL?
Branches need equal column counts, corresponding columns require compatible types and meanings, alignment is positional, and final names come from the first query.
Does UNION ALL guarantee branch order?
No. Only a final ORDER BY guarantees presentation order.
Can UNION ALL be used with joins and aggregates?
Yes. Each branch may include joins, filters, grouping, subqueries, and expressions, and an outer query may aggregate the combined result.
When should UNION be preferred?
Use UNION when complete duplicate output rows represent the same logical result and the requirement explicitly needs a distinct list.
How are NULL rows handled?
All rows are preserved, including repeated rows containing null values.
How do you prevent double counting?
Define complete business keys, preserve source lineage, make branches mutually exclusive where required, and validate overlap before aggregation.
Practical Exercises
Build a complete order feed from current and archive tables, a global API log from three regions, and a workforce directory from employees and contractors. Add source labels, normalize types, establish deterministic ordering, and calculate totals across the complete relation.
Then introduce overlapping records intentionally. Write a validation query that identifies them by business key. Compare plans for UNION ALL and UNION, and explain whether removing duplicates would correct the data or merely hide the overlap.
Quick Reference
-- Preserve all rows
SELECT columns FROM source_a
UNION ALL
SELECT columns FROM source_b;
-- Add lineage
SELECT columns, 'A' AS source FROM source_a
UNION ALL
SELECT columns, 'B' AS source FROM source_b;
-- Process the combined stream
SELECT grouping_key, SUM(amount)
FROM (
SELECT grouping_key, amount FROM source_a
UNION ALL
SELECT grouping_key, amount FROM source_b
) AS combined
GROUP BY grouping_key
ORDER BY grouping_key;
Key Takeaway
UNION ALL combines compatible query outputs while preserving every occurrence. It is the natural operator for transactions, events, logs, test executions, historical partitions, regional feeds, and ETL staging because equal values do not necessarily represent duplicate facts.
Correct use requires more than matching column counts. Define the row grain, align meaning and types, preserve source identity, normalize business units, filter early, order explicitly, aggregate at the right stage, and test source overlap. When those decisions are deliberate, UNION ALL provides both accurate semantics and efficient execution for large real-world datasets.