Short answer
UNION combines two result sets and removes duplicate rows, which forces the database to sort or hash the entire combined set to deduplicate; UNION ALL simply concatenates and keeps duplicates.
Explanation
If you know the sets are disjoint or duplicates are acceptable, always use UNION ALL because it skips the expensive dedup step and can stream results. Using UNION out of habit when UNION ALL would do is a common, easily fixed performance leak on large result sets.
Common mistakes
- Using UNION when duplicates are impossible, paying for a sort.
- Not knowing UNION removes duplicates.