UNION vs UNION ALL vs OR

Before (Inefficient Query)

-- UNION removes duplicates (slower)
SELECT email, name FROM customers
UNION
SELECT email, name FROM employees;

After (Optimized Query)

-- UNION ALL keeps all rows (faster)
SELECT email, name FROM customers
UNION ALL
SELECT email, name FROM employees;

Why This Optimization Works

1. UNION Processingi: UNION must combine all Rows from both queries, sort all results, and remove duplicate values. This requires additional CPU and memory.

2. UNION ALL Processingi: UNION ALL simply appends all rows from the first query, then appends all rows from the second query. No sorting, no deduplication.

3. When Duplicates Don't Matteri: If you know there are no duplicates (or don't care), always use UNION ALL.

When to Use Each

Use UNION ALL when:

• Tables have no overlapping data
• You're certain duplicates don't exist
• Performance is critical
• Combining similar log tables

Use UNION when:

• You need unique values
• Tables might have duplicates
• Simplicity is preferred over performance

Practical Example

-- UNION: Gets unique customers + employees
SELECT email, 'customer' as type FROM customers
UNION
SELECT email, 'employee' as type FROM employees;

-- UNION ALL: Faster but may have duplicates
SELECT email, 'customer' as type FROM customers
UNION ALL
SELECT email, 'employee' as type FROM employees;

-- Alternative: Use WHERE to filter duplicates
SELECT email, 'customer' as type FROM customers
WHERE email NOT IN (SELECT email FROM employees)
UNION ALL
SELECT email, 'employee' as type FROM employees;

PostgreSQL Optimization Note: PostgreSQL can often optimize UNION to UNION ALL internally when it detects no duplicates are possible. Check the execution plan.

UNION ALL vs OR

Depending on the database engine being used, there are times where switching between UNION ALL and OR could offer a performance boost depending on the scenario.

When to Choose OR

Choose OR when:

• The number of alternative conditions is small
• You want the optimizer to do a single index seek with minimal combining overhead

When to Choose UNION ALL

Choose UNION ALL if:

• You have many alternative conditions (a long list of OR's)
• You want each branch to run in parallel and avoid hash/sort overhead
• The overhead of combining via OR would outweigh the cost of multiple seeks

Practical Example

-- Using OR (few conditions - efficient)
SELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';

-- Using UNION ALL (many conditions - more efficient)
SELECT * FROM orders WHERE status = 'pending'
UNION ALL
SELECT * FROM orders WHERE status = 'processing'
UNION ALL
SELECT * FROM orders WHERE status = 'shipped'
UNION ALL
SELECT * FROM orders WHERE status = 'delivered'
UNION ALL
SELECT * FROM orders WHERE status = 'cancelled'
UNION ALL
SELECT * FROM orders WHERE status = 'refunded';

Summary

Run a query planner (EXPLAIN ANALYZE) to determine which approach provides the best performance for your specific scenario. The optimal choice depends on the number of conditions, index availability, and your database engine.