SQL UNION: How to Combine Query Results (With Examples)

SQL UNION combines the results of two or more SELECT statements into a single result set. It stacks rows from multiple queries vertically, as long as the queries return the same number of columns with compatible data types.

Basic Syntax

SELECT column1, column2 FROM table_a
UNION
SELECT column1, column2 FROM table_b;

Rules:

• Both queries must return the same number of columns
• Columns must have compatible data types (or be castable)
• Column names come from the first SELECT statement
• UNION removes duplicate rows by default

UNION vs. UNION ALL

-- UNION removes duplicates
SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- Returns each city once, even if it appears in both tables

-- UNION ALL keeps everything
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
-- Returns all cities from both, including duplicates

Performance tip: Always use UNION ALL unless you specifically need deduplication. The sort operation for UNION can be expensive on large result sets.

Common Use Cases

Combining Similar Tables

When data is split across tables (by time period, region, or source):

-- Combine data from partitioned tables
SELECT order_id, customer_id, amount, order_date FROM orders_2024
UNION ALL
SELECT order_id, customer_id, amount, order_date FROM orders_2025
UNION ALL
SELECT order_id, customer_id, amount, order_date FROM orders_2026;

Creating a Unified Activity Feed

SELECT
    'email' AS activity_type,
    email_date AS activity_date,
    recipient AS contact,
    subject AS description
FROM sent_emails
UNION ALL
SELECT
    'call' AS activity_type,
    call_date,
    phone_number,
    notes
FROM call_logs
UNION ALL
SELECT
    'meeting' AS activity_type,
    meeting_date,
    attendees,
    title
FROM meetings
ORDER BY activity_date DESC;

Building Reference Lists

-- All unique contacts from multiple sources
SELECT email, name, 'customer' AS source FROM customers
UNION
SELECT email, name, 'lead' FROM leads
UNION
SELECT email, name, 'partner' FROM partners;

Handling Conditional Logic Across Tables

-- Active subscriptions from two billing systems
SELECT customer_id, plan_name, mrr, 'stripe' AS source
FROM stripe_subscriptions
WHERE status = 'active'
UNION ALL
SELECT customer_id, plan_name, mrr, 'chargebee'
FROM chargebee_subscriptions
WHERE status = 'active';

Advanced Patterns

UNION with Aggregation

-- Total revenue across multiple revenue tables
SELECT 'subscriptions' AS source, SUM(amount) AS revenue
FROM subscription_payments WHERE payment_date >= '2026-01-01'
UNION ALL
SELECT 'one-time', SUM(amount)
FROM one_time_payments WHERE payment_date >= '2026-01-01'
UNION ALL
SELECT 'services', SUM(amount)
FROM service_invoices WHERE invoice_date >= '2026-01-01';

UNION in a CTE

WITH all_interactions AS (
    SELECT customer_id, interaction_date, 'support' AS type
    FROM support_tickets
    UNION ALL
    SELECT customer_id, login_date, 'login'
    FROM user_sessions
    UNION ALL
    SELECT customer_id, purchase_date, 'purchase'
    FROM orders
)
SELECT
    customer_id,
    COUNT(*) AS total_interactions,
    COUNT(DISTINCT type) AS interaction_types,
    MAX(interaction_date) AS last_activity
FROM all_interactions
GROUP BY customer_id;

UNION for Generating Rows

-- Generate a static lookup without a table
SELECT 1 AS month_num, 'January' AS month_name
UNION ALL SELECT 2, 'February'
UNION ALL SELECT 3, 'March'
UNION ALL SELECT 4, 'April'
UNION ALL SELECT 5, 'May'
UNION ALL SELECT 6, 'June';

UNION vs. JOIN

UNION and JOIN solve different problems:

-- UNION: I have customers in two tables, combine them into one list
SELECT name, email FROM us_customers
UNION ALL
SELECT name, email FROM eu_customers;

-- JOIN: I want customer info alongside their orders
SELECT c.name, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id;

Common Mistakes

1. Column Count Mismatch

-- ERROR: different number of columns
SELECT id, name, email FROM customers
UNION
SELECT id, name FROM suppliers;  -- Missing one column

-- FIX: add NULL or a placeholder
SELECT id, name, email FROM customers
UNION
SELECT id, name, NULL AS email FROM suppliers;

2. Data Type Mismatch

-- May cause issues if amount is NUMERIC and name is VARCHAR
SELECT amount FROM orders
UNION
SELECT name FROM customers;  -- Type mismatch

-- Fix: cast to compatible types
SELECT CAST(amount AS VARCHAR) FROM orders
UNION
SELECT name FROM customers;

3. Forgetting ORDER BY Applies to the Whole Result

-- ORDER BY goes at the end, applies to combined result
SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'supplier' AS type FROM suppliers
ORDER BY name;  -- Sorts the entire combined result

4. Using UNION When UNION ALL Is Appropriate

If you know the source tables have no overlap (different primary key ranges, different time periods), always use UNION ALL. The deduplication step in UNION is wasted computation.

Performance Tips

1. Filter before UNION, not after. Apply WHERE clauses in each SELECT, not on the combined result.
2. Use UNION ALL when possible. Saves a sort and dedup operation.
3. Limit columns. Only SELECT the columns you actually need.
4. Consider alternatives. If you are always combining the same tables, create a view.

Summary

UNION is essential for combining data that lives across multiple tables, systems, or time periods into a single queryable result set.