SQL Query for UNION: Complete Guide with Examples | Free
Write Your First SQL Query in 10 Seconds—Free
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
— Combine active and inactive customers 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
— UNION (removes duplicates) SELECT product_id, product_name FROM products_2023 UNION SELECT product_id, product_name FROM products_2024;
— UNION ALL (keeps duplicates) 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
— Combine sales from multiple channels 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
— Complex analysis using CTEs and UNION 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
— Proper type casting in UNION 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
— UNION: All customers from both sets SELECT customer_id FROM online_customers UNION SELECT customer_id FROM retail_customers;
— INTERSECT: Customers present in both sets SELECT customer_id FROM online_customers INTERSECT SELECT customer_id FROM
UNION vs JOIN
— Using UNION SELECT product_id, product_name, ‘Current’ as status FROM current_products UNION SELECT product_id, product_name, ‘Discontinued’ as status FROM discontinued_products;
— Equivalent using JOIN 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
— Combine sales data from multiple years 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.
Start your free trial
Share this
More Articles
More Articles
More Articles
TOOLS
Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide
Build Your Own AI Agent Team in 15 Min — Free OpenClaw Guide
Feb 5, 2026
TOOLS
OpenClaw AI Assistant: Local 24/7 Automation Guide 2026
OpenClaw AI Assistant: Local 24/7 Automation Guide 2026
Feb 4, 2026
TOOLS
SQL WITH Clause (CTE): Complete Guide with Examples
SQL WITH Clause (CTE): Complete Guide with Examples
Jan 14, 2026
TOOLS
MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion
MySQL to PostgreSQL Migration: Complete 2026 Guide with Syntax Conversion
Jan 14, 2026
TOOLS
SQL vs Excel: When Should You Make the Switch? [2026]
SQL vs Excel: When Should You Make the Switch? [2026]
Jan 14, 2026
Copyright © AI2sql 2026
Cross Regions Technology
13553 Atlantic Blvd, Suite 201
FL 32225
Company