Difference Between UNION and UNION ALL?

Question

Accepted Answer

UNION and UNION ALL both combine the result sets of two or more SELECT queries into one, but UNION removes duplicate rows from the combined output while UNION ALL keeps every row, including duplicates.

Key Points:
• UNION performs an implicit deduplication pass, which requires sorting or hashing the combined rows and adds overhead.
• UNION ALL simply concatenates result sets without checking for duplicates, making it faster.
• Both require the combined SELECT statements to have the same number of columns with compatible data types.
• Use UNION ALL by default for performance unless you specifically need duplicates removed.

Example:
Combining a list of current customers and a list of former customers with UNION would remove anyone who appears in both lists, while UNION ALL would keep that person's row twice if you specifically wanted a count of both memberships.

Code Example:
```
SELECT customer_name FROM current_customers
UNION
SELECT customer_name FROM former_customers;

SELECT customer_name FROM current_customers
UNION ALL
SELECT customer_name FROM former_customers;
```

Interview Tip:
A concise interview answer is:

"UNION combines result sets and removes duplicate rows, while UNION ALL combines them and keeps every row including duplicates. I default to UNION ALL for performance since it skips the deduplication step, and only use UNION when I actually need distinct combined results."