What is the difference between UNION and UNION ALL

· Category: SQL & Databases

Short answer

UNION combines result sets and removes duplicate rows. UNION ALL combines result sets without deduplication, making it faster when duplicates are not a concern.

Key differences

  • Deduplication: UNION performs a sort or hash to eliminate duplicates. UNION ALL simply concatenates results.
  • Performance: UNION ALL is significantly faster because it skips the deduplication step.
  • Use case: Use UNION when uniqueness matters. Use UNION ALL when you know the sets are disjoint or when duplicates are acceptable.

When to use each

  • Use UNION when merging similar tables that may contain overlapping records.
  • Use UNION ALL for appending historical partitions or log tables where overlap is impossible.r