UNION

The UNION operator is one of the most frequently used SET operators in SQL. It combines the results of two or more SELECT statements into a single result set while automatically removing duplicate rows.

In this tutorial, you will learn:

• What UNION does and how it works
• The syntax for using UNION
• How UNION handles duplicates
• Performance considerations when using UNION
• Practical examples with our e-commerce dataset

UNION combines the results of two or more SELECT statements and returns only distinct (unique) rows.

Key Characteristics

Basic Syntax

SELECT column1, column2, ...
FROM table1
[WHERE condition]

UNION

SELECT column1, column2, ...
FROM table2
[WHERE condition];

Multiple UNIONs

SELECT columns FROM table1
UNION
SELECT columns FROM table2
UNION
SELECT columns FROM table3;

With ORDER BY

SELECT city FROM customers
UNION
SELECT city FROM sellers
ORDER BY city;  -- Applied to the combined result

With LIMIT

SELECT city FROM customers
UNION
SELECT city FROM sellers
ORDER BY city
LIMIT 10;  -- Limits the final result

Full Row Comparison

UNION determines uniqueness by comparing all columns in each row:

Important: All Columns Must Match

A row is only considered a duplicate if every single column matches:

-- These are NOT duplicates:
('sao paulo', 'SP', 'Customer')
('sao paulo', 'SP', 'Seller')
-- The third column differs, so both rows are kept

NULL Handling

In UNION, two NULL values are treated as equal:

(NULL, 'SP')  +  (NULL, 'SP')  →  (NULL, 'SP')  -- One row kept

Let's practice using UNION with our e-commerce dataset.

Why UNION Can Be Slower

To remove duplicates, UNION must:

1. Execute both queries and collect all rows
2. Sort or hash the combined result
3. Compare rows to find and remove duplicates
4. Return the distinct result

Query 1 (1000 rows) + Query 2 (1000 rows)
         ↓
   Combined (2000 rows)
         ↓
   Deduplication process
         ↓
   Final Result (possibly fewer rows)

When to Use UNION vs UNION ALL

Performance Tip: Consider UNION ALL + DISTINCT

For complex queries, sometimes this pattern is more efficient:

-- Instead of UNION:
SELECT DISTINCT *
FROM (
    SELECT columns FROM table1
    UNION ALL
    SELECT columns FROM table2
);

-- This can sometimes be optimized better by the query planner

1. Creating Master Reference Lists

-- All payment types ever used
SELECT DISTINCT payment_type
FROM olist_order_payments_dataset;

-- If payment types came from multiple sources:
SELECT payment_type FROM payment_source_1
UNION
SELECT payment_type FROM payment_source_2;

2. Combining Historical and Current Data

-- Combine archived and current orders
SELECT order_id, order_status, order_date
FROM current_orders
UNION
SELECT order_id, order_status, order_date
FROM archived_orders;

3. Creating Lookup Tables

-- All unique locations for a dropdown menu
SELECT city, state FROM customers
UNION
SELECT city, state FROM sellers
ORDER BY state, city;

4. Reporting Across Entities

-- Activity report from multiple entity types
SELECT 'Order' AS type, order_id AS id, order_purchase_timestamp AS date
FROM orders
UNION
SELECT 'Review' AS type, review_id AS id, review_creation_date AS date
FROM reviews
ORDER BY date;

Mistake 1: Expecting UNION to Preserve Duplicates

❌ Assumption: UNION keeps all rows

-- If both tables have 'sao paulo, SP', UNION returns it once
SELECT city, state FROM customers  -- Has 'sao paulo, SP'
UNION
SELECT city, state FROM sellers;   -- Also has 'sao paulo, SP'
-- Result: Only ONE row for 'sao paulo, SP'

✅ Solution: Use UNION ALL if you need all rows

Mistake 2: Different Column Counts

❌ Wrong:

SELECT city, state, zip FROM customers
UNION
SELECT city, state FROM sellers;  -- Missing column!

✅ Correct:

SELECT city, state, zip FROM customers
UNION
SELECT city, state, NULL AS zip FROM sellers;  -- Add placeholder

Mistake 3: Mismatched Column Order

❌ Wrong:

SELECT city, state FROM customers
UNION
SELECT state, city FROM sellers;  -- Columns are swapped!

This will run but produce incorrect results because columns are matched by position.

Mistake 4: ORDER BY in Individual Queries

❌ Wrong:

SELECT city FROM customers ORDER BY city
UNION
SELECT city FROM sellers;

✅ Correct:

SELECT city FROM customers
UNION
SELECT city FROM sellers
ORDER BY city;  -- Only at the end

Key Takeaways

1. UNION combines results from multiple SELECT statements

2. Automatically removes duplicates - returns only unique rows

3. Duplicate detection compares ALL columns in each row

4. Syntax:

SELECT columns FROM table1
UNION
SELECT columns FROM table2
[ORDER BY column];

5. Requirements:

• Same number of columns
• Compatible data types
• Columns matched by position

6. Performance: Slower than UNION ALL due to deduplication

7. Common use cases:

• Creating master reference lists
• Combining data from multiple sources
• Building lookup tables
• Cross-entity reporting

UNION vs UNION ALL Quick Reference

What's Next?

Next, we'll learn about UNION ALL, which is similar to UNION but preserves all rows including duplicates.