Introduction to SQL Union Operations
The SQL UNION operator combines results from multiple SELECT statements into a single result set. This powerful feature allows you to merge data from different tables or queries while eliminating duplicates.
Quick Example
SELECT customer_name, 'Active' as status
FROM active_customers
UNION
SELECT customer_name, 'Inactive' as status
FROM inactive_customers
ORDER BY
For more tools and AI-powered SQL query solutions, visit our AI SQL Tools page.
Basic Syntax and Functionality
UNION Syntax
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM
UNION vs UNION ALL
SELECT product_id, product_name
FROM products_2023
UNION
SELECT product_id, product_name
FROM products_2024;
SELECT product_id, product_name
FROM products_2023
UNION ALL
SELECT product_id, product_name
FROM
For additional SQL query optimization tips, check out our SQL Query Optimization Guide.
Advanced Techniques and Best Practices
1. Combining Different Tables
SELECT
order_date,
amount,
'Online' as channel
FROM online_sales
WHERE order_date >= '2024-01-01'
UNION
SELECT
order_date,
amount,
'Retail' as channel
FROM retail_sales
WHERE order_date >= '2024-01-01'
ORDER BY
2. Using WITH Clause
WITH online_metrics AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') as month,
SUM(amount) as revenue,
COUNT(*) as orders
FROM online_sales
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
retail_metrics AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') as month,
SUM(amount) as revenue,
COUNT(*) as orders
FROM retail_sales
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
month,
revenue,
orders,
'Online' as channel
FROM online_metrics
UNION ALL
SELECT
month,
revenue,
orders,
'Retail' as channel
FROM retail_metrics
ORDER BY month,
3. Column Type Matching
SELECT
CAST(product_id AS VARCHAR) as id,
product_name
FROM products
UNION
SELECT
order_id,
order_reference
FROM
Comparative Analysis with Other Set Operators
UNION vs INTERSECT
SELECT customer_id FROM online_customers
UNION
SELECT customer_id FROM retail_customers;
SELECT customer_id FROM online_customers
INTERSECT
SELECT customer_id FROM
UNION vs JOIN
SELECT product_id, product_name, 'Current' as status
FROM current_products
UNION
SELECT product_id, product_name, 'Discontinued' as status
FROM discontinued_products;
SELECT
COALESCE(c.product_id, d.product_id) as product_id,
COALESCE(c.product_name, d.product_name) as product_name,
CASE
WHEN c.product_id IS NOT NULL THEN 'Current'
ELSE 'Discontinued'
END as status
FROM current_products c
FULL OUTER JOIN discontinued_products d
ON
Real-world Applications
1. Sales Analysis
SELECT
DATE_FORMAT(order_date, '%Y') as year,
COUNT(*) as total_orders,
SUM(amount) as total_revenue,
'Historical' as data_type
FROM sales_history
WHERE order_date < '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y')
UNION
SELECT
DATE_FORMAT(order_date, '%Y') as year,
COUNT(*) as total_orders,
SUM(amount) as total_revenue,
'Current' as data_type
FROM current_sales
WHERE order_date >= '2024-01-01'
GROUP BY DATE_FORMAT(order_date, '%Y')
ORDER BY year
Best Practices
Column Matching
Ensure same number of columns
Match data types
Use consistent column names
Performance
Use UNION ALL when duplicates are acceptable
Create proper indexes
Consider materialized views for large datasets
Maintenance
Document complex unions
Use CTEs for clarity
Consider performance impact
FAQs
Q: When should I use UNION vs UNION ALL?
A: Use UNION ALL when duplicates are acceptable or known to be impossible, as it's more efficient.
Q: Can I use UNION with different column names?
A: Yes, but the column names from the first SELECT statement will be used in the result.
Learn more about AI-powered SQL tools at AI2SQL.
