MySQL union and union all

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements and removes duplicate rows from the result set. By default, UNION performs deduplication.

UNION ALL

The UNION ALL operator also combines the result sets of two or more SELECT statements but does not remove duplicate rows, retaining all results.

Differences

Deduplication

• UNION performs deduplication, keeping only unique rows.
• UNION ALL does not deduplicate, retaining all rows, including duplicates.

Performance

• UNION incurs higher overhead due to deduplication, resulting in slightly lower performance.
• UNION ALL has higher performance as it does not perform deduplication.