UNION removes duplicates (expensive operation). UNION ALL keeps them (fast).
UNION (Slow):
SELECT Name FROM ActiveUsers UNION SELECT Name FROM InactiveUsers; -- Removes duplicates: Sorts, scans, filters -- Slow on large datasets!
UNION ALL (Fast):
SELECT Name FROM ActiveUsers UNION ALL SELECT Name FROM InactiveUsers; -- Keeps duplicates: No sorting or filtering -- Much faster!
Performance: UNION ALL can be 2-10x faster depending on data size!
When to Use: If you know there are no duplicates, or duplicates are acceptable, always use UNION ALL.