Understanding SQL UNION: Combine Query Results Like a Pro

Have you ever needed to combine data from multiple tables or queries into a single result set? Maybe you're merging customer lists from different regions, or combining current and archived records. That's exactly what SQL UNION is designed for—and it's simpler than you might think.

In this guide, we'll explore everything you need to know about SQL UNION, including when to use it, how it differs from UNION ALL, and best practices to avoid common pitfalls.

What is SQL UNION?

The UNION operator combines the result sets of two or more SELECT statements into a single result set. Think of it as stacking multiple tables on top of each other, but with one important rule: UNION automatically removes duplicate rows.

Here's the basic syntax:

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

Key Rules for UNION

Before we dive into examples, here are the essential rules you must follow:

1. Same number of columns: All SELECT statements must have the same number of columns
2. Compatible data types: Columns in the same position must have compatible data types
3. Column order matters: The order of columns must be the same in all queries
4. Column names from first query: The result set uses column names from the first SELECT statement

UNION vs UNION ALL: What's the Difference?

This is one of the most common questions about UNION, and understanding the difference is crucial for performance:

-- UNION removes duplicates
SELECT city FROM customers_usa
UNION
SELECT city FROM customers_canada;

-- UNION ALL keeps all rows
SELECT city FROM customers_usa
UNION ALL
SELECT city FROM customers_canada;

Pro Tip: If you know your data won't have duplicates, or if duplicates are acceptable, always use UNION ALL for better performance.

Practical Example: Combining Customer Data

Let's work with a realistic scenario. Imagine you have customers in two different tables—active customers and archived customers—and you want to create a complete mailing list.

Notice a few important things in this example:

1. We added a literal column 'Active' and 'Archived' to distinguish the source of each row
2. Even though Bob Smith appears in both tables, UNION removes the duplicate
3. We can use ORDER BY at the end to sort the final combined result

Try changing UNION to UNION ALL in the playground above and see how Bob Smith appears twice!

UNION with Different Tables

UNION isn't limited to tables with the same structure. You can combine results from completely different tables as long as the columns match in number and type.

Here we're combining products from two different tables with different column names (product_name vs item_name, price vs cost). The result set uses the column names from the first query.

Combining More Than Two Queries

You can chain multiple UNION operations together to combine three or more result sets:

SELECT name, email FROM customers_usa
UNION
SELECT name, email FROM customers_canada
UNION
SELECT name, email FROM customers_mexico
UNION
SELECT name, email FROM customers_uk;

Each UNION operation is processed left to right, and duplicates are removed at each step (if using UNION).

Common Use Cases for UNION

Here are some real-world scenarios where UNION shines:

1. Merging Historical and Current Data

SELECT order_id, order_date, total
FROM current_orders
WHERE order_date >= '2025-01-01'
UNION ALL
SELECT order_id, order_date, total
FROM archived_orders
WHERE order_date >= '2025-01-01';

2. Creating Comprehensive Reports

-- Sales report from multiple regions
SELECT 'North' AS region, SUM(sales) AS total_sales
FROM north_sales
UNION ALL
SELECT 'South', SUM(sales)
FROM south_sales
UNION ALL
SELECT 'East', SUM(sales)
FROM east_sales
UNION ALL
SELECT 'West', SUM(sales)
FROM west_sales;

3. Combining Different Query Conditions

-- Get VIP customers and high-value customers
SELECT customer_id, name, 'VIP Member' AS reason
FROM customers
WHERE membership_level = 'VIP'
UNION
SELECT customer_id, name, 'High Spender' AS reason
FROM customers
WHERE total_purchases > 10000;

Performance Considerations

Understanding the performance implications of UNION can help you write more efficient queries:

UNION is Slower Than UNION ALL

Because UNION must identify and remove duplicates, it requires additional processing:

1. Sorting: The database sorts the combined result set
2. Comparison: Adjacent rows are compared to find duplicates
3. Filtering: Duplicate rows are removed

If you don't need duplicate removal, UNION ALL can be significantly faster, especially with large datasets.

Use WHERE Clauses to Filter Early

Filter data in each SELECT statement before combining:

-- Good: Filter before UNION
SELECT name, email FROM customers WHERE country = 'USA'
UNION
SELECT name, email FROM partners WHERE country = 'USA';

-- Less efficient: Filter after UNION
SELECT name, email, country FROM customers
UNION
SELECT name, email, country FROM partners
WHERE country = 'USA';  -- This won't work as intended!

Important: You cannot use WHERE on the final UNION result directly. Use a subquery or CTE if you need to filter the combined results:

-- Correct way to filter UNION results
SELECT * FROM (
  SELECT name, email, country FROM customers
  UNION
  SELECT name, email, country FROM partners
) AS combined
WHERE country = 'USA';

Common Mistakes to Avoid

1. Mismatched Column Counts

-- ❌ ERROR: Different number of columns
SELECT name, email FROM customers
UNION
SELECT name FROM partners;

-- ✅ CORRECT: Same number of columns
SELECT name, email FROM customers
UNION
SELECT name, email FROM partners;

2. Incompatible Data Types

-- ❌ ERROR: Incompatible types (TEXT vs INTEGER)
SELECT name, email FROM customers
UNION
SELECT customer_id, order_id FROM orders;

-- ✅ CORRECT: Cast to compatible types
SELECT name, email FROM customers
UNION
SELECT CAST(customer_id AS TEXT), CAST(order_id AS TEXT) FROM orders;

3. Forgetting ORDER BY Placement

-- ❌ ERROR: ORDER BY in wrong place
SELECT name FROM customers ORDER BY name
UNION
SELECT name FROM partners;

-- ✅ CORRECT: ORDER BY at the end
SELECT name FROM customers
UNION
SELECT name FROM partners
ORDER BY name;

UNION vs Other Combining Methods

It's important to understand when to use UNION versus other SQL combining techniques:

UNION vs JOIN

• UNION: Combines rows from multiple queries (vertical stacking)
• JOIN: Combines columns from multiple tables (horizontal merging)

-- UNION: Stacks rows vertically
SELECT name FROM customers
UNION
SELECT name FROM employees;

-- JOIN: Merges columns horizontally
SELECT c.name, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

UNION vs UNION ALL vs INTERSECT vs EXCEPT

Best Practices

Here are some tips to help you use UNION effectively:

1. Use UNION ALL when possible: If duplicates don't matter, UNION ALL is faster
2. Keep column names consistent: Use aliases to make your queries more readable
3. Add source indicators: Include a literal column to identify which query each row came from
4. Filter early: Apply WHERE clauses in individual SELECT statements, not after UNION
5. Consider indexes: Ensure columns used in WHERE clauses are indexed for better performance
6. Test with small datasets first: Verify your logic before running on production data
7. Document complex UNIONs: Add comments explaining why you're combining specific queries

Interactive Challenge

Now it's your turn! Try to solve this challenge using the playground below:

Challenge: Create a query that combines all employees and contractors, showing their names, roles, and employment type. Sort the results by name.

SELECT 
  name,
  role,
  'Employee' AS employment_type
FROM employees
UNION
SELECT 
  name,
  role,
  'Contractor' AS employment_type
FROM contractors
ORDER BY name;

Conclusion

SQL UNION is a powerful tool for combining data from multiple sources into a single result set. Remember these key takeaways:

• UNION removes duplicates, while UNION ALL keeps them (and is faster)
• All SELECT statements must have the same number of columns with compatible data types
• Use ORDER BY at the end to sort the combined results
• Filter early in individual SELECT statements for better performance
• Add source indicators to track where each row originated

Whether you're merging customer lists, combining historical data, or creating comprehensive reports, mastering UNION will make your SQL toolkit much more versatile. Start with simple examples, understand the performance implications, and you'll be combining query results like a pro in no time!

Ready to practice more? Try our interactive SQL playground or explore our other tutorials on SQL fundamentals.