Home / SQL / UNION

UNION

Overview

The UNION operator combines the result sets of two or more SELECT statements into a single result set. UNION removes duplicate rows, returning only unique rows.

UNION is useful for combining data from multiple tables or queries that have the same structure. It's different from JOINs, which combine columns - UNION combines rows.

Basic Syntax

UNION Syntax

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

Requirements for UNION

For UNION to work, both SELECT statements must:

Have the same number of columns
Have compatible data types for corresponding columns
Return columns in the same order

UNION vs UNION ALL

Understanding the difference:

Operator	Description	Duplicate Handling
UNION	Combines results and removes duplicates	Removes duplicates
UNION ALL	Combines results without removing duplicates	Keeps duplicates

Examples

Example 1: Basic UNION

Combine two SELECT queries

SELECT name, email, city
FROM Customers
UNION
SELECT name, email, city
FROM Suppliers;

Returns: All unique names, emails, and cities from both tables (duplicates removed).

Example 2: UNION ALL (Keep Duplicates)

Include duplicates

SELECT name, email, city
FROM Customers
UNION ALL
SELECT name, email, city
FROM Suppliers;

Returns: All rows from both tables, including duplicates (faster than UNION).

Example 3: Multiple UNIONs

Combine multiple queries

SELECT name, email FROM Customers
UNION
SELECT name, email FROM Suppliers
UNION
SELECT name, email FROM Employees;

UNION with Different Column Names

Column names come from the first SELECT statement:

Column name handling

SELECT customer_name AS name, email
FROM Customers
UNION
SELECT supplier_name AS name, email
FROM Suppliers;
-- Result columns will be named 'name' and 'email'

UNION with ORDER BY

ORDER BY must be placed at the end and applies to the entire result set:

Sort UNION results

SELECT name, email FROM Customers
UNION
SELECT name, email FROM Suppliers
ORDER BY name;

UNION vs JOIN

Understanding the difference:

UNION vs JOIN

UNION:
- Combines ROWS from multiple queries
- Adds rows vertically
- Requires same number of columns
- Removes duplicates (unless UNION ALL)

JOIN:
- Combines COLUMNS from multiple tables
- Adds columns horizontally
- Can have different column counts
- Based on relationships between tables

Common Use Cases

Combining customer and supplier lists
Merging data from similar tables
Creating comprehensive reports from multiple sources
Data consolidation
Alternative to FULL JOIN in MySQL

Performance Considerations

UNION ALL is faster: Doesn't remove duplicates
UNION is slower: Must sort and remove duplicates
Use UNION ALL when possible: If duplicates don't matter
Index columns: Can improve UNION performance

Best Practices

Use UNION ALL when duplicates are OK: Faster performance
Ensure column compatibility: Same number and compatible types
Use aliases consistently: First SELECT sets column names
Test with small datasets first: Verify logic before large queries
Document the purpose: Make it clear why UNION is needed

Next Steps

Learn about grouping data with GROUP BY, or explore conditional logic with CASE.

← Full Join Group By →