Paradime | UNION / UNION ALL

SQL Keywords

UNION / UNION ALL

Feb 23, 2026

min read

Category: Set Operations

Platform Support:

✅ Snowflake | ✅ BigQuery | ✅ Databricks

Description

UNION combines the result sets of two or more SELECT statements into a single result set. UNION removes duplicate rows, while UNION ALL keeps all rows including duplicates. Essential for combining data from multiple sources or tables with similar structures.

Syntax

-- UNION (removes duplicates)
SELECT columns FROM table1
UNION
SELECT columns FROM table2;

-- UNION ALL (keeps duplicates)
SELECT columns FROM table1
UNION ALL
SELECT columns FROM

-- UNION (removes duplicates)
SELECT columns FROM table1
UNION
SELECT columns FROM table2;

-- UNION ALL (keeps duplicates)
SELECT columns FROM table1
UNION ALL
SELECT columns FROM

-- UNION (removes duplicates)
SELECT columns FROM table1
UNION
SELECT columns FROM table2;

-- UNION ALL (keeps duplicates)
SELECT columns FROM table1
UNION ALL
SELECT columns FROM

Requirements:

Same number of columns in each SELECT
Corresponding columns must have compatible data types
Column names from first SELECT are used in result

Platform-Specific Notes

All Platforms:

UNION and UNION ALL work identically
UNION ALL is faster (no deduplication)
ORDER BY applies to entire result (place at end)
Can union more than 2 queries

Example 1: UNION vs UNION ALL

All Platforms:

-- UNION (removes duplicates)
SELECT customer_id, name, 'Premium' as tier
FROM premium_customers
UNION
SELECT customer_id, name, 'Standard' as tier
FROM standard_customers;

-- UNION ALL (keeps duplicates)
SELECT customer_id, name, 'Premium' as tier
FROM premium_customers
UNION ALL
SELECT customer_id, name, 'Standard' as tier
FROM

-- UNION (removes duplicates)
SELECT customer_id, name, 'Premium' as tier
FROM premium_customers
UNION
SELECT customer_id, name, 'Standard' as tier
FROM standard_customers;

-- UNION ALL (keeps duplicates)
SELECT customer_id, name, 'Premium' as tier
FROM premium_customers
UNION ALL
SELECT customer_id, name, 'Standard' as tier
FROM

-- UNION (removes duplicates)
SELECT customer_id, name, 'Premium' as tier
FROM premium_customers
UNION
SELECT customer_id, name, 'Standard' as tier
FROM standard_customers;

-- UNION ALL (keeps duplicates)
SELECT customer_id, name, 'Premium' as tier
FROM premium_customers
UNION ALL
SELECT customer_id, name, 'Standard' as tier
FROM

Sample Data:

premium_customers:

customer_id	name
1	John Smith
2	Maria Garcia

standard_customers:

customer_id	name
2	Maria Garcia
3	Sarah Johnson

Result with UNION (duplicates removed):

customer_id	name	tier
1	John Smith	Premium
2	Maria Garcia	Premium
2	Maria Garcia	Standard
3	Sarah Johnson	Standard

Result with UNION ALL (all rows kept):

customer_id	name	tier
1	John Smith	Premium
2	Maria Garcia	Premium
2	Maria Garcia	Standard
3	Sarah Johnson	Standard

Note: In this case both return same results, but UNION ALL is faster

Example 2: Combining Historical and Current Data

All Platforms:

SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    'Current' as data_source
FROM current_orders
WHERE order_date >= '2024-01-01'

UNION ALL

SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    'Archive' as data_source
FROM archived_orders
WHERE order_date >= '2024-01-01'

ORDER BY order_date DESC,

SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    'Current' as data_source
FROM current_orders
WHERE order_date >= '2024-01-01'

UNION ALL

SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    'Archive' as data_source
FROM archived_orders
WHERE order_date >= '2024-01-01'

ORDER BY order_date DESC,

SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    'Current' as data_source
FROM current_orders
WHERE order_date >= '2024-01-01'

UNION ALL

SELECT 
    order_id,
    customer_id,
    order_date,
    total,
    'Archive' as data_source
FROM archived_orders
WHERE order_date >= '2024-01-01'

ORDER BY order_date DESC,

Sample Data:

current_orders:

order_id	customer_id	order_date	total
1001	101	2024-01-15	250.00
1002	102	2024-01-20	180.00

archived_orders:

order_id	customer_id	order_date	total
900	103	2024-01-10	320.00
901	101	2024-01-12	450.00

Result:

order_id	customer_id	order_date	total	data_source
1002	102	2024-01-20	180.00	Current
1001	101	2024-01-15	250.00	Current
901	101	2024-01-12	450.00	Archive
900	103	2024-01-10	320.00	Archive

Example 3: Multi-Source Contact List

All Platforms:

SELECT 
    email,
    name,
    'Customer' as source,
    signup_date as source_date
FROM customers
WHERE email IS NOT NULL

UNION

SELECT 
    email,
    subscriber_name as name,
    'Newsletter' as source,
    subscription_date as source_date
FROM newsletter_subscribers
WHERE email IS NOT NULL

UNION

SELECT 
    contact_email as email,
    contact_name as name,
    'Lead' as source,
    created_date as source_date
FROM sales_leads
WHERE contact_email IS NOT NULL

ORDER BY

SELECT 
    email,
    name,
    'Customer' as source,
    signup_date as source_date
FROM customers
WHERE email IS NOT NULL

UNION

SELECT 
    email,
    subscriber_name as name,
    'Newsletter' as source,
    subscription_date as source_date
FROM newsletter_subscribers
WHERE email IS NOT NULL

UNION

SELECT 
    contact_email as email,
    contact_name as name,
    'Lead' as source,
    created_date as source_date
FROM sales_leads
WHERE contact_email IS NOT NULL

ORDER BY

SELECT 
    email,
    name,
    'Customer' as source,
    signup_date as source_date
FROM customers
WHERE email IS NOT NULL

UNION

SELECT 
    email,
    subscriber_name as name,
    'Newsletter' as source,
    subscription_date as source_date
FROM newsletter_subscribers
WHERE email IS NOT NULL

UNION

SELECT 
    contact_email as email,
    contact_name as name,
    'Lead' as source,
    created_date as source_date
FROM sales_leads
WHERE contact_email IS NOT NULL

ORDER BY

Sample Data:

customers:

email	name	signup_date
john@email.com	John Smith	2024-01-15
maria@email.com	Maria Garcia	2024-01-20

newsletter_subscribers:

email	subscriber_name	subscription_date
sarah@email.com	Sarah Johnson	2024-01-18
john@email.com	John S.	2024-01-10

sales_leads:

contact_email	contact_name	created_date
ahmed@email.com	Ahmed Hassan	2024-01-22

Result (UNION removes exact duplicates):

email	name	source	source_date
ahmed@email.com	Ahmed Hassan	Lead	2024-01-22
john@email.com	John S.	Newsletter	2024-01-10
john@email.com	John Smith	Customer	2024-01-15
maria@email.com	Maria Garcia	Customer	2024-01-20
sarah@email.com	Sarah Johnson	Newsletter	2024-01-18

Example 4: Quarterly Sales Aggregation

All Platforms:

SELECT 
    'Q1 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'

UNION ALL

SELECT 
    'Q2 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'

UNION ALL

SELECT 
    'Q3 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-07-01' AND '2024-09-30'

ORDER BY

SELECT 
    'Q1 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'

UNION ALL

SELECT 
    'Q2 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'

UNION ALL

SELECT 
    'Q3 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-07-01' AND '2024-09-30'

ORDER BY

SELECT 
    'Q1 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31'

UNION ALL

SELECT 
    'Q2 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-04-01' AND '2024-06-30'

UNION ALL

SELECT 
    'Q3 2024' as quarter,
    SUM(total) as total_sales,
    COUNT(*) as order_count,
    AVG(total) as avg_order_value
FROM orders
WHERE order_date BETWEEN '2024-07-01' AND '2024-09-30'

ORDER BY

Result:

quarter	total_sales	order_count	avg_order_value
Q1 2024	125000.00	450	277.78
Q2 2024	142000.00	520	273.08
Q3 2024	138000.00	495	278.79

Example 5: Creating Comprehensive Reports

All Platforms:

-- Detailed rows
SELECT 
    product_category,
    product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY product_category, product_name

UNION ALL

-- Category subtotals
SELECT 
    product_category,
    'CATEGORY TOTAL' as product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY product_category

UNION ALL

-- Grand total
SELECT 
    'GRAND TOTAL' as product_category,
    '' as product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales

ORDER BY 
    product_category,
    CASE WHEN product_name = 'CATEGORY TOTAL' THEN 'ZZZZ' ELSE product_name END

-- Detailed rows
SELECT 
    product_category,
    product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY product_category, product_name

UNION ALL

-- Category subtotals
SELECT 
    product_category,
    'CATEGORY TOTAL' as product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY product_category

UNION ALL

-- Grand total
SELECT 
    'GRAND TOTAL' as product_category,
    '' as product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales

ORDER BY 
    product_category,
    CASE WHEN product_name = 'CATEGORY TOTAL' THEN 'ZZZZ' ELSE product_name END

-- Detailed rows
SELECT 
    product_category,
    product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY product_category, product_name

UNION ALL

-- Category subtotals
SELECT 
    product_category,
    'CATEGORY TOTAL' as product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales
GROUP BY product_category

UNION ALL

-- Grand total
SELECT 
    'GRAND TOTAL' as product_category,
    '' as product_name,
    SUM(quantity) as units_sold,
    SUM(revenue) as total_revenue
FROM sales

ORDER BY 
    product_category,
    CASE WHEN product_name = 'CATEGORY TOTAL' THEN 'ZZZZ' ELSE product_name END

Sample Data (sales table):

product_category	product_name	quantity	revenue
Electronics	Laptop	50	50000
Electronics	Mouse	200	6000
Furniture	Desk	30	9000
Furniture	Chair	80	16000

Result:

product_category	product_name	units_sold	total_revenue
Electronics	Laptop	50	50000
Electronics	Mouse	200	6000
Electronics	CATEGORY TOTAL	250	56000
Furniture	Chair	80	16000
Furniture	Desk	30	9000
Furniture	CATEGORY TOTAL	110	25000
GRAND TOTAL		360	81000

UNION vs UNION ALL Performance

Aspect	UNION	UNION ALL
Duplicates	Removed	Kept
Performance	Slower (sorts/deduplicates)	Faster
Use when	Need unique rows	Know no duplicates OR duplicates acceptable
Memory	Higher (deduplication)	Lower
Typical speedup	Baseline	2-10x faster

Common Use Cases

Combine partitioned tables: Merge data from monthly/yearly tables
Multi-source aggregation: Combine data from different systems
Historical analysis: Mix current and archived data
Contact consolidation: Merge customer lists from multiple sources
Reporting: Create summary rows with detail rows
A/B testing: Combine test and control groups

UNION with Different Column Names

All Platforms:

-- Column names from first SELECT are used
SELECT 
    customer_id as id,
    customer_name as name,
    'Customer' as type
FROM customers

UNION ALL

SELECT 
    vendor_id,      -- maps to 'id'
    vendor_name,    -- maps to 'name'
    'Vendor'        -- maps to 'type'
FROM

-- Column names from first SELECT are used
SELECT 
    customer_id as id,
    customer_name as name,
    'Customer' as type
FROM customers

UNION ALL

SELECT 
    vendor_id,      -- maps to 'id'
    vendor_name,    -- maps to 'name'
    'Vendor'        -- maps to 'type'
FROM

-- Column names from first SELECT are used
SELECT 
    customer_id as id,
    customer_name as name,
    'Customer' as type
FROM customers

UNION ALL

SELECT 
    vendor_id,      -- maps to 'id'
    vendor_name,    -- maps to 'name'
    'Vendor'        -- maps to 'type'
FROM

Other Set Operations

INTERSECT - Returns only rows that exist in both queries:

SELECT customer_id FROM customers_2023
INTERSECT
SELECT customer_id FROM

SELECT customer_id FROM customers_2023
INTERSECT
SELECT customer_id FROM

SELECT customer_id FROM customers_2023
INTERSECT
SELECT customer_id FROM

EXCEPT/MINUS - Returns rows from first query not in second:

-- Snowflake/Databricks: MINUS
SELECT customer_id FROM all_customers
MINUS
SELECT customer_id FROM active_customers;

-- BigQuery: EXCEPT
SELECT customer_id FROM all_customers
EXCEPT DISTINCT
SELECT customer_id FROM

-- Snowflake/Databricks: MINUS
SELECT customer_id FROM all_customers
MINUS
SELECT customer_id FROM active_customers;

-- BigQuery: EXCEPT
SELECT customer_id FROM all_customers
EXCEPT DISTINCT
SELECT customer_id FROM

-- Snowflake/Databricks: MINUS
SELECT customer_id FROM all_customers
MINUS
SELECT customer_id FROM active_customers;

-- BigQuery: EXCEPT
SELECT customer_id FROM all_customers
EXCEPT DISTINCT
SELECT customer_id FROM

Performance Tips

Prefer UNION ALL: Use unless you specifically need deduplication
Filter before UNION: Apply WHERE clauses to each query
Index appropriately: Index columns used in WHERE clauses
Limit result size: Use LIMIT/TOP after ORDER BY
Consider views: Create view for frequently unioned queries
Avoid over-unioning: Don't union unnecessarily large result sets

Best Practices

Use consistent column order: Makes queries more maintainable
Add source indicators: Include column showing data source
Document business logic: Explain why data is being combined
Test data types: Ensure compatible types across all SELECTs
Use parentheses: Group complex unions for clarity
ORDER BY at end only: Can't order individual SELECTs
Validate results: Check for unexpected duplicates or missing rows

Interested to Learn More?
Try Out the Free 14-Days Trial

Start free trial

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Learn

Feb 18, 2026

BigQuery Global Queries: How to Run Cross-Region SQL in 2026

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Analytics

Feb 18, 2026

Context Engineering and AI Quality for Data Teams

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Product

Feb 18, 2026

Accelerate Analytics Development with Paradime and Tableau

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Experience Analytics for the AI-Era

Start your 14-day trial today - it's free and no credit card needed

Start for free

Platform

Resources

ADD-ONs

Industries

About

Legal

Made with ❤️ in San Francisco ・ London

*dbt® and dbt Core® are federally registered trademarks of dbt Labs, Inc. in the United States and various jurisdictions around the world. Paradime is not a partner of dbt Labs. All rights therein are reserved to dbt Labs. Paradime is not a product or service of or endorsed by dbt Labs, Inc.

Start for free

Platform

Resources