Course contentsShow
← Data Science
Lesson 1000 of 2,14526. Advanced SQLFree lesson

UNION ALL: Preserving Duplicates

Understand when and why to use UNION ALL to keep all rows including duplicates for better performance.

UNION ALL: Preserving Duplicates

You'll learn when and why UNION ALL is faster and more appropriate than UNION when you need to keep all rows, including duplicates.

The Core Difference

In the previous lesson, you learned that UNION combines result sets and automatically removes duplicates. UNION ALL does the same combining operation but keeps every single row from both queries, duplicates and all.

Think of it like merging two shopping lists: UNION crosses out any repeated items to give you a unique list, while UNION ALL simply stacks both lists together as-is, even if "milk" appears on both.

Basic Syntax

SELECT product_id, product_name
FROM january_sales

UNION ALL

SELECT product_id, product_name
FROM february_sales;

If the same product was sold in both months, it appears twice in the results—exactly what you want if you're counting total transactions.

When to Use UNION ALL

  1. When duplicates are meaningful: Combining sales records, event logs, or time-series data where each row represents a distinct occurrence
  2. When you know there are no duplicates: Combining completely different datasets (e.g., customers from different regions with non-overlapping IDs)
  3. For performance: UNION ALL skips the expensive duplicate-checking step, making it significantly faster on large datasets

Performance Impact

UNION must sort and compare rows to find duplicates. UNION ALL just appends results—no extra work. On large tables, this can mean the difference between seconds and minutes.

Key Takeaway: Use UNION ALL when you need all rows preserved or when you know duplicates don't exist—it's faster and more explicit about your intent than UNION.