UNION in SQL

Introduction

Applications often store logically similar records in separate places. A company may have current and former employee tables, an online store may keep domestic and international orders separately, and an analytics platform may divide live events from archived history. Reporting still needs one coherent list. SQL provides the UNION set operator for exactly this kind of vertical combination.

UNION joins the rows returned by two or more SELECT statements and presents them as one result set. Unlike a table join, it does not place columns from related rows side by side. It stacks compatible results and, by default, removes rows that are completely duplicated. That simple definition hides important design questions about column count, data types, aliases, nulls, ordering, aggregation, performance, and source identity.

This tutorial develops those ideas through connected examples. It explains the rules that make a union valid, shows how duplicate elimination actually works, demonstrates combinations with filters, joins, subqueries, common table expressions, and aggregates, and finishes with production scenarios, debugging guidance, performance analysis, and interview questions.

What Is UNION in SQL?

UNION is a SQL set operator that combines the output of multiple queries into one logical table. Each participating query produces rows with the same number of columns in corresponding positions. The database aligns the first column of one result with the first column of every other result, aligns the second columns, and continues in that manner. It then removes duplicate combined rows before returning the final result.

SELECT column1, column2
FROM first_table
UNION
SELECT column1, column2
FROM second_table;

The result column names normally come from the first SELECT. The later queries contribute values, not final output names. A union can combine different tables, repeated queries against the same table, derived results, or even constant rows. The tables do not need a declared relationship because compatibility is determined by the selected output columns.

Why UNION Is Needed

Separate storage often reflects lifecycle, geography, ownership, performance, or retention requirements rather than different business meaning. Current and archived transactions are still transactions. Customers acquired online and in stores are still customers. Events collected from several regions still belong in a global operational report. UNION lets a query expose these physically separate sources through one consistent row shape.

For example, assume current_employees and former_employees both contain employee identifiers and names. The following statement returns each distinct employee record found in either source:

SELECT employee_id, employee_name
FROM current_employees
UNION
SELECT employee_id, employee_name
FROM former_employees;

If the row (103, 'Carol') appears in both tables, it appears once in the output. If the same identifier has a different name in one source, both rows survive because duplicate elimination compares the entire selected row, not only the identifier.

How UNION Works Conceptually

Each SELECT is logically evaluated to produce an intermediate result. Those rows are appended into one stream, after which duplicate rows are eliminated. The optimizer is free to implement the operation with sorting, hashing, index access, parallel workers, or transformations. Therefore, the conceptual sequence explains semantics but does not promise a physical execution order.

First SELECT  -> Result A
Second SELECT -> Result B
Result A + Result B
        -> remove complete duplicate rows
        -> final result

Suppose the first query returns Alice, Bob, and Carol, while the second returns Carol, David, and Emma. The combined stream has six rows, but the distinct result has five because Carol is duplicated. No output order is guaranteed unless the complete union has an explicit final ORDER BY.

Sample Tables Used in the Examples

The examples use regional employee tables, customer channels, and order partitions. The employee sources contain employee_id, employee_name, and department. Customer sources contain customer_id, customer_name, and city. Order sources contain order_id, customer_id, order_date, and total_amount.

SourceExample purposeNatural row grain
employees_usUS employeesOne row per employee
employees_indiaIndia employeesOne row per employee
online_customersOnline channel customersOne row per customer
store_customersStore channel customersOne row per customer
domestic_ordersDomestic ordersOne row per order
international_ordersInternational ordersOne row per order

Thinking about grain is essential. A union is meaningful only when every branch represents the same kind of row. Combining an employee row with a department summary merely because both contain three columns creates a syntactically valid but semantically misleading result.

Basic UNION Example

To retrieve unique employee names across the two regional tables, select the same logical attribute from both sources:

SELECT employee_name
FROM employees_us
UNION
SELECT employee_name
FROM employees_india;

If Carol appears in both sources, her name appears once. Notice that selecting only the name defines duplication at the name level. Two unrelated employees who share a name would collapse into one row. If identity matters, include the employee identifier as well.

UNION with Multiple Columns

A union may return any practical number of columns as long as every branch returns the same count. Duplicate comparison then uses the complete row.

SELECT employee_id, employee_name, department
FROM employees_us
UNION
SELECT employee_id, employee_name, department
FROM employees_india;

Two records are duplicates only when all three selected values compare as duplicates under the database's set-operation rules. If Carol has the same identifier and name but one row says Finance and another says IT, both rows remain. UNION does not infer that employee ID is the business key and choose one version.

UNION vs UNION ALL

UNION ALL appends all rows and performs no duplicate elimination. It preserves repeated records both within a branch and across branches. This difference affects correctness and cost.

SELECT customer_id, customer_name
FROM online_customers
UNION ALL
SELECT customer_id, customer_name
FROM store_customers;
BehaviorUNIONUNION ALL
Duplicate rowsRemovedPreserved
Additional workDistinct processingUsually simple append
Typical useOne unique logical listComplete event or transaction stream
RiskMay hide meaningful repeatsMay double count overlapping sources

Choose from business meaning, not habit. Use UNION when duplicate output rows represent the same logical fact and must collapse. Use UNION ALL when every source row matters or when sources are guaranteed not to overlap. Transactions, events, audit entries, and test executions usually require UNION ALL.

Rule One: Every Query Needs the Same Column Count

The database matches columns by position, so each query must return the same number of columns. This is invalid:

SELECT employee_id, employee_name
FROM employees_us
UNION
SELECT employee_id
FROM employees_india;

When a source does not have a corresponding value, supply a typed placeholder such as NULL or a constant. The placeholder should express a real absence in a common output model rather than conceal incompatible data.

SELECT employee_id, employee_name, department
FROM employees_us
UNION
SELECT employee_id, employee_name, CAST(NULL AS VARCHAR(50))
FROM contractors;

Rule Two: Corresponding Types Must Be Compatible

Columns in the same position must have types the database can reconcile. An integer identifier generally cannot be combined safely with an arbitrary date or JSON document. Engines apply type precedence and implicit conversion differently, so a query that works in one product may fail or change values in another.

Use explicit CAST operations when sources store the same concept differently. Explicit conversion documents intent and makes failures easier to diagnose.

SELECT CAST(order_id AS VARCHAR(30)) AS record_id,
       total_amount
FROM domestic_orders
UNION ALL
SELECT external_order_code,
       total_amount
FROM partner_orders;

Choose a target type wide enough for every source. Converting identifiers to a narrow character type can silently truncate in some environments, while converting text to numbers can fail on malformed values.

Rule Three: Column Position Defines Meaning

Aliases do not align branches by name. The first output column receives the first value from every query, even if a later query uses another alias. Reversing name and department produces misleading data without necessarily causing an error.

-- Correct positional alignment
SELECT employee_name, department
FROM employees_us
UNION
SELECT employee_name, department
FROM employees_india;

Review each branch vertically and keep the same column order, meaning, units, and formatting. In long unions, list columns explicitly and avoid SELECT *, because schema changes can alter the output shape unexpectedly.

Rule Four: Output Names Come from the First Query

The first SELECT establishes final column labels in most SQL systems. Give those expressions clear aliases and treat later aliases only as documentation.

SELECT employee_id AS person_id,
       employee_name AS person_name
FROM employees_us
UNION
SELECT contractor_id,
       contractor_name
FROM contractors;

The final columns are person_id and person_name. Those names can be referenced by the final ordering clause where the database supports alias ordering.

Rule Five: UNION Does Not Guarantee Sorting

Duplicate elimination may use a sort internally, but that implementation detail does not establish a presentation order. A plan may change after an upgrade, index addition, statistics refresh, or parallel execution. Apply one ORDER BY to the complete combined query.

SELECT employee_id, employee_name, department
FROM employees_us
UNION
SELECT employee_id, employee_name, department
FROM employees_india
ORDER BY department, employee_name;

Parentheses or derived tables may be needed when individual branches require their own limiting or ordering. Even then, only the final ordering determines the displayed sequence of the complete result.

Filtering Before Combining Results

Each branch can have its own WHERE condition. Filtering early reduces rows that must pass through the set operation and keeps source-specific rules close to their source.

SELECT employee_id, employee_name, department
FROM employees_us
WHERE department = 'IT'
UNION
SELECT employee_id, employee_name, department
FROM employees_india
WHERE department = 'IT';

The conditions do not have to be identical. A migration report might select active rows from the live table and recently retired rows from an archive. What must stay consistent is the meaning of the resulting columns and row grain.

Combining Three or More Queries

Set operators can connect several branches. The same compatibility rules apply to every query.

SELECT customer_id, customer_name, 'ONLINE' AS source
FROM online_customers
UNION ALL
SELECT customer_id, customer_name, 'STORE' AS source
FROM store_customers
UNION ALL
SELECT customer_id, customer_name, 'PARTNER' AS source
FROM partner_customers;

Source labels are valuable for lineage and debugging. They also make otherwise identical business rows distinct under UNION, because the source value becomes part of the compared row. Decide whether identity across sources or source membership is more important before adding the label.

Using UNION with the Same Table

Separate tables are not required. Multiple branches can classify records from one table, especially when categories need different rules or labels.

SELECT order_id, total_amount, 'HIGH_VALUE' AS segment
FROM orders
WHERE total_amount >= 1000
UNION ALL
SELECT order_id, total_amount, 'STANDARD' AS segment
FROM orders
WHERE total_amount < 1000;

Ensure category predicates are intentionally exclusive or overlapping. If a row can satisfy both branches, UNION ALL emits it twice with different labels, while UNION still preserves both because the labels differ.

How NULL Values Behave

During duplicate elimination, set operations treat rows containing nulls as duplicates when corresponding values match for distinctness purposes. This differs from ordinary predicates, where NULL = NULL is unknown. Therefore, two single-column null rows normally collapse into one under UNION and remain two under UNION ALL.

SELECT city FROM online_customers
UNION
SELECT city FROM store_customers;

If both sources contain null cities, the result generally contains one null row. With multiple columns, the complete combination determines duplication. A null city paired with customer 201 is different from a null city paired with customer 202.

Constants, Calculated Columns, and Source Labels

Branches may return constants and expressions as long as corresponding outputs are compatible.

SELECT order_id,
       total_amount,
       total_amount * 0.08 AS estimated_tax,
       'DOMESTIC' AS order_scope
FROM domestic_orders
UNION ALL
SELECT order_id,
       total_amount,
       total_amount * 0.15 AS estimated_tax,
       'INTERNATIONAL' AS order_scope
FROM international_orders;

Apply comparable units. Combining dollars and euros in one amount column without conversion is syntactically valid but analytically wrong. The common row model should include currency when amounts are not normalized.

UNION with JOIN

Every branch can contain joins. The join constructs a source-specific result, and the union then stacks those compatible results.

SELECT o.order_id, c.customer_name, o.total_amount
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
FROM international_orders AS o
JOIN customers AS c
  ON c.customer_id = o.customer_id;

Keep join cardinality consistent. If one branch accidentally joins each order to many address rows while another returns one row per order, the union inherits that duplication. Validate each branch independently before combining them.

UNION with GROUP BY and HAVING

Branches can aggregate independently and then return compatible summaries.

SELECT department,
       COUNT(*) AS employee_count,
       'US' AS region
FROM employees_us
GROUP BY department
HAVING COUNT(*) >= 2
UNION ALL
SELECT department,
       COUNT(*) AS employee_count,
       'INDIA' AS region
FROM employees_india
GROUP BY department
HAVING COUNT(*) >= 2;

This result has one row per qualifying department and region. It does not calculate a global department count. To aggregate across all regions, combine detail rows first and apply the final aggregation outside the union.

Aggregation After UNION

A derived table turns the combined result into an input for another query. This pattern is essential when totals must span all sources.

SELECT customer_id,
       SUM(total_amount) AS lifetime_amount
FROM (
    SELECT customer_id, total_amount
    FROM domestic_orders
    UNION ALL
    SELECT customer_id, total_amount
    FROM international_orders
) AS all_orders
GROUP BY customer_id;

UNION ALL is normally correct here because two equal transaction amounts are still two transactions. Using UNION could remove a legitimate order when the selected customer and amount happen to match. Include the transaction key whenever identity matters, and do not use duplicate elimination as a substitute for data-quality rules.

UNION with Subqueries and Derived Tables

A union can appear inside a subquery, and its branches can contain subqueries. The surrounding query then filters, joins, or summarizes the combined relation.

SELECT person_id, person_name
FROM (
    SELECT employee_id AS person_id,
           employee_name AS person_name
    FROM employees_us
    UNION
    SELECT employee_id, employee_name
    FROM employees_india
) AS people
WHERE person_name LIKE 'A%';

Use a descriptive alias and keep every branch readable. Deeply nested unions become difficult to review; a CTE is often clearer when several stages need names.

UNION with Common Table Expressions

A CTE can give the combined dataset a meaningful name and allow several downstream operations to reuse the same logical result.

WITH all_orders AS (
    SELECT order_id, customer_id, order_date,
           total_amount, 'DOMESTIC' AS source
    FROM domestic_orders
    UNION ALL
    SELECT order_id, customer_id, order_date,
           total_amount, 'INTERNATIONAL' AS source
    FROM international_orders
)
SELECT source,
       COUNT(*) AS order_count,
       SUM(total_amount) AS total_sales
FROM all_orders
GROUP BY source;

A CTE improves organization but does not automatically materialize or accelerate the data. Review the execution plan because engines may inline, materialize, or otherwise transform it.

Date Filtering, Limiting, and Pagination

Time filters should normally be applied in each branch so partitions can use indexes or pruning. Then apply final pagination to a deterministically ordered combined result.

SELECT order_id, customer_id, order_date, total_amount
FROM (
    SELECT order_id, customer_id, order_date, total_amount
    FROM domestic_orders
    WHERE order_date >= DATE '2026-01-01'
    UNION ALL
    SELECT order_id, customer_id, order_date, total_amount
    FROM international_orders
    WHERE order_date >= DATE '2026-01-01'
) AS recent_orders
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 25 ROWS ONLY;

Syntax for limiting rows differs among MySQL, PostgreSQL, SQL Server, and Oracle. For stable pagination, include a unique tiebreaker. Offset pagination can become slow and unstable on changing data; keyset pagination based on the last date and ID is often stronger.

UNION in E-Commerce Systems

E-commerce platforms use unions for cross-channel customer views, current plus archived orders, domestic plus international fulfillment, and events collected by regional services. A unified order feed should normalize identifiers, timestamps, currency, status vocabulary, and source labels before exposure.

SELECT order_id, customer_id, total_amount,
       currency_code, order_date, 'WEB' AS channel
FROM web_orders
UNION ALL
SELECT order_id, customer_id, total_amount,
       currency_code, order_date, 'STORE' AS channel
FROM store_orders;

Preserve every order with UNION ALL. If identifiers can collide between channels, define a composite key using source and local ID rather than trusting the local ID alone.

UNION in Banking and Financial Reporting

Financial systems frequently combine deposits, withdrawals, adjustments, and archived ledger partitions. These rows must not be removed merely because selected values happen to match. Use UNION ALL, preserve transaction identifiers and source systems, and normalize signs and currencies deliberately.

SELECT transaction_id, account_id,
       amount AS signed_amount,
       posted_at, 'CREDIT' AS transaction_type
FROM credits
UNION ALL
SELECT transaction_id, account_id,
       -amount AS signed_amount,
       posted_at, 'DEBIT' AS transaction_type
FROM debits;

Reconciliation should occur after the complete event stream is assembled. If duplicate ingestion is possible, resolve it using an explicit business key and documented precedence rule rather than a broad UNION.

UNION in Employee and Student Management

Human-resources reports may combine employees, contractors, and alumni while preserving role type. Education systems may combine active students, graduates, and external learners. A common projection exposes the shared attributes and uses null placeholders for fields that genuinely do not apply.

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,
       CAST(NULL AS VARCHAR(50)), 'CONTRACTOR'
FROM contractors;

A unified list does not mean the source entities are interchangeable in every workflow. Keep authorization, lifecycle, and ownership rules outside or alongside the reporting projection.

UNION in Software Testing and API Monitoring

Quality platforms combine UI, API, mobile, and performance executions into dashboards. Monitoring systems combine request observations from regions or services. Every execution is evidence, so repeated status and duration values must remain separate.

SELECT test_id, execution_time, status,
       duration_ms, 'API' AS suite
FROM api_test_results
UNION ALL
SELECT test_id, execution_time, status,
       duration_ms, 'UI' AS suite
FROM ui_test_results;

After combining, the outer query can calculate pass rates, percentiles, failure trends, or service-level indicators. Ensure timestamps use a consistent zone and status values are mapped to one vocabulary.

UNION in Data Migration

Migration teams use unions to compare legacy and target data, assemble batches from several old systems, and produce exception reports. Source labels and stable lineage keys are mandatory because migration errors must be traced back to the originating record.

Do not use UNION to make duplicate migration rows disappear unless duplicate elimination is the documented requirement. First expose overlaps with UNION ALL, classify them, and resolve conflicts with explicit precedence, survivorship, and audit rules.

UNION vs JOIN

A union stacks rows vertically; a join combines related columns horizontally. If one query returns customer ID and name while another returns customer ID and order total, joining on customer ID answers a relationship question. Unioning them would require artificial null columns and create unrelated row types.

QuestionUse UNIONUse JOIN
Need more rows of the same shape?YesNo
Need columns from related records?NoYes
Requires a relationship condition?Not inherentlyUsually
Typical resultOne taller relationOne wider relation

UNION vs INTERSECT, EXCEPT, and OR

UNION returns rows from either input. INTERSECT returns rows common to both inputs. EXCEPT, called MINUS in some systems, returns rows in the first input but not the second. Product support and duplicate variants differ, so check the target database.

OR combines predicates while scanning one logical query source. Two branches connected by UNION combine query outputs. An optimizer may transform one form into another, but they are not automatically equivalent, particularly when projections, joins, duplicates, or null behavior differ.

How Duplicate Elimination Is Implemented

A database commonly removes duplicates by sorting the combined rows or building a hash structure. Both strategies consume CPU and memory; large results may spill to temporary storage. Wide rows, expensive collations, skewed data, and insufficient memory increase cost. Distributed systems may also move rows across workers.

The plan can show an append or concatenation operator followed by a distinct sort, hash aggregate, or unique operation. Exact operator names vary. Use actual execution statistics to see row counts, spills, memory grants, and time rather than guessing from SQL text.

Performance Strategies

Start by selecting only required columns. Wide rows cost more to scan, transfer, compare, sort, and hash. Push selective predicates into each branch so indexes, partition pruning, and storage filters can reduce input early. Use UNION ALL when duplicate elimination is unnecessary, then aggregate or deduplicate at the correct business grain if needed.

Indexes help the scans and filters inside branches, but they do not make an unnecessary global distinct free. Align data types to avoid conversions that disable index access. For recurring cross-partition reporting, consider a partitioned table, view, materialized view, or warehouse model instead of repeating a very large union in every report.

Benchmark with realistic volume and overlap. A union that is fast when inputs are disjoint may behave differently when most rows duplicate. Test concurrency, temp-space pressure, sort memory, network movement, and downstream pagination.

Reading an EXPLAIN Plan

Inspect each branch first. Confirm expected indexes, predicates, join methods, and estimated row counts. Then inspect the combining operator and any duplicate-removal stage. Compare estimates with actual rows because underestimation can cause weak memory grants and spills.

For UNION ALL, the plan often resembles a concatenation. For UNION, expect an additional uniqueness operation. If the database already knows branches cannot overlap, it may optimize some work, but developers should not rely on an undocumented transformation.

Common Mistakes

  • Returning different numbers of columns from participating queries.
  • Aligning fields by apparent alias rather than by position.
  • Combining incompatible types and relying on risky implicit conversion.
  • Assuming duplicate elimination uses a primary key instead of the complete selected row.
  • Using UNION for transaction totals and accidentally removing legitimate repeated facts.
  • Assuming an internal distinct sort guarantees final presentation order.
  • Adding DISTINCT to every branch when the outer UNION already removes duplicates.
  • Using a union where a relationship join is required.
  • Dropping source identity when local identifiers can overlap.
  • Using duplicate elimination to hide unresolved data-quality problems.
  • Combining rows that have different grains, units, currencies, or time zones.
  • Paginating without deterministic final ordering.

Testing UNION Queries

Create test data covering disjoint sources, exact duplicates, partial duplicates, null values, type boundaries, identifier collisions, empty branches, and records present in three or more sources. Verify row count and business identity, not only sample values. Test each branch independently before testing the combined result.

For financial and event data, assert that repeated facts remain present under UNION ALL. For distinct reference lists, assert that full duplicate rows collapse. For source labels, verify that identical records remain separate when lineage is intentionally part of the output.

Best Practices

  1. Define the common row grain before writing any branch.
  2. List columns explicitly and keep their order consistent.
  3. Use clear aliases in the first query.
  4. Normalize types, units, currencies, timestamps, and status values.
  5. Choose UNION or UNION ALL from duplicate semantics.
  6. Preserve source identity whenever local keys can overlap.
  7. Push safe filters into branches before combining them.
  8. Apply one deterministic final ORDER BY.
  9. Aggregate after combining when the metric spans all sources.
  10. Review actual plans and production-scale behavior.
  11. Use CTEs or views to make long unions maintainable.
  12. Document overlap assumptions and data ownership.

Interview Questions and Answers

What is UNION in SQL?

It is a set operator that combines compatible results from two or more SELECT statements and removes complete duplicate rows.

What is the difference between UNION and UNION ALL?

UNION removes duplicate rows, while UNION ALL keeps every row and is usually less expensive because it avoids global distinct processing.

What are the main UNION rules?

Every query must return the same number of columns, corresponding positions need compatible types and meanings, final names come from the first query, and sorting requires a final ORDER BY.

Does UNION remove duplicates by primary key?

No. It compares all selected output columns. A differing non-key value makes the row distinct.

Can UNION combine unrelated tables?

Yes, provided their selected outputs form the same meaningful row shape. No foreign-key relationship is required.

Can UNION be used with WHERE, JOIN, and GROUP BY?

Yes. Each branch is a query and may contain filters, joins, grouping, having conditions, and subqueries.

How does UNION handle NULL?

For duplicate elimination, rows with nulls in corresponding positions can be considered duplicates when all selected values otherwise match.

Is UNION slower than UNION ALL?

It is commonly more expensive because it must remove duplicates, but actual performance depends on data, row width, plans, memory, and the database engine.

Can UNION be used in a CTE or subquery?

Yes. The combined relation can be named in a CTE or enclosed as a derived table for later filtering, joining, aggregation, and pagination.

Practical Exercises

Practice creating a unique contact list from customer and supplier tables, a complete transaction stream from current and archive partitions, and a regional employee report with source labels. Then calculate customer totals after combining domestic and international orders. For every exercise, state the intended grain and explain whether duplicate rows represent noise or valid repeated facts.

Next, deliberately reverse two columns in one branch, introduce a null placeholder, mix numeric and text identifiers, and remove the final ordering. Observe the resulting errors or misleading output. Compare execution plans for UNION and UNION ALL on a large overlapping dataset.

Quick Reference

-- Unique combined rows
SELECT columns FROM source_a
UNION
SELECT columns FROM source_b;

-- Preserve every row
SELECT columns FROM source_a
UNION ALL
SELECT columns FROM source_b;

-- Process the complete combined result
SELECT grouping_column, SUM(amount)
FROM (
    SELECT grouping_column, amount FROM source_a
    UNION ALL
    SELECT grouping_column, amount FROM source_b
) AS combined
GROUP BY grouping_column
ORDER BY grouping_column;

Key Takeaway

UNION is the correct tool when several queries produce the same kind of row and those rows need to become one distinct result. Its apparent simplicity depends on disciplined alignment: equal column counts, compatible types, consistent position and meaning, deliberate duplicate semantics, normalized values, and explicit final ordering.

Use UNION ALL when every fact must survive or sources are known to be disjoint. Preserve lineage, aggregate at the correct stage, test nulls and overlaps, and inspect real execution plans. When those decisions are explicit, set operations become a reliable foundation for reporting, migration, monitoring, testing, and cross-system data integration.