Difference between UNION and UNION ALL is that:
- UNION: only keeps unique records
- UNION ALL: keeps all records, including duplicates
UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records. UNION first performs a sorting operation and eliminates of the records that are duplicated across all columns before finally returning the combined data set.
Example for UNION and UNION ALL :
UNION =>
UNION ALL =>
UNION or UNION ALL have the same basic requirements of the data being combined:
1. There must be the same number of columns retrieved in each SELECT statement to be combined.
2. The columns retrieved must be in the same order in each SELECT statement.
3. The columns retrieved must be of similar data types.
Because the UNION ALL operator does not remove duplicate rows, it runs faster than the UNION operator.
