UNION vs. UNION ALL

Set Operations in SQL

In SQL, combining datasets is a common requirement — whether you’re merging reports, consolidating logs, or integrating tables in ETL pipelines. But there’s a subtle detail many overlook: the difference between UNION and UNION ALL. This single choice can make your queries 10x faster or 10x slower.

1. UNION: Deduplication Comes at a Cost

The UNION operator merges the results of two or more queries and removes duplicate rows.
That sounds useful — but deduplication isn’t free.

• How it works:
  SQL engines internally sort and compare all rows to eliminate duplicates.
• Impact:
  On large datasets, this sorting step can consume lots of memory and CPU time.

Use Case:

• When your business logic requires unique results — e.g., getting a distinct list of customer IDs from multiple regions.

2. UNION ALL: Faster, Simpler, Safer

UNION ALL skips the deduplication step. It simply stacks all rows together, even if there are duplicates.

• Why it’s faster:
  There’s no sorting, no comparisons, no extra overhead.
• Best suited for: