How to Use UNION in SQL

Use UNION when you need to combine the results of multiple SELECT queries into one result set. This is perfect for merging similar tables, combining archived data, or building unified feeds.

What you’ll build or solve

You’ll learn how to use UNION in SQL to merge compatible query results. You’ll also know when to use UNION ALL instead.

When this approach works best

This approach is the right choice when multiple queries return the same logical shape.

Common real-world scenarios include:

• Active + archived users
• Regional sales tables
• Event logs from multiple systems
• CRM + billing contact feeds
• Legacy + migrated data

This is a bad idea when the data belongs in one normalized table and should be combined with JOIN.

Prerequisites

You only need:

• Two or more SELECT queries
• Matching column count
• Compatible column data types

Step-by-step instructions

Step 1: Stack compatible SELECT queries

Each query must return the same number of columns in the same order.

SELECT email
FROM active_users

UNION

SELECT email
FROM archived_users;

This combines both result sets and removes duplicates.

Use UNION ALL to keep duplicates.

SELECT email
FROM crm_contacts

UNION ALL

SELECT email
FROM billing_contacts;

This is usually faster because it skips deduplication.

What to look for:

• Column counts must match
• Data types must be compatible
• UNION removes duplicates
• UNION ALL keeps everything
• Great for merged feeds

Examples you can copy

Current + archived orders

SELECT order_id
FROM orders

UNION

SELECT order_id
FROM archived_orders;

Multi-region revenue rows

SELECT total
FROM sales_usa

UNION ALL

SELECT total
FROM sales_uk;

Legacy + new users

SELECT email
FROM legacy_users

UNION

SELECT email
FROM new_users;

Common mistakes and how to fix them

Mistake 1: Mismatched column counts

What the reader might do:

SELECT email, name
UNION
SELECT email

Why it breaks: both queries must return the same number of columns.

Corrected approach:

Align the selected columns.

Mistake 2: Expecting duplicates to remain with UNION

What the reader might do:

UNION

Why it breaks: duplicates are removed automatically.

Corrected approach:

Use UNION ALL.

Mistake 3: Mixing incompatible data types

What the reader might do:

Combine INT IDs with text labels in the same column position.

Why it breaks: implicit casting may fail or produce poor results.

Corrected approach:

Align compatible data types.

Troubleshooting

If SQL errors, confirm the same column count.

If duplicates disappear, switch to UNION ALL.

If sorting is needed, add one final ORDER BY after the last query.

If the logic models relationships, consider JOIN instead.

Quick recap

• Use UNION to combine result sets
• Queries must return the same shape
• UNION removes duplicates
• UNION ALL keeps duplicates
• Add one final ORDER BY at the end