Difference between UNION and UNION ALL

UNION and UNION ALL are used to combine the results of select queries. UNION ALL logs all results, including duplicates. UNION does not log duplicate results. UNION ALL is faster than UNION. In reporting and application, UNION is better than UNION ALL, since duplicate results are not suitable.

What is UNION?

The UNION command is used to select the results of two queries that have the same number of columns. Does not select duplicate results, only different entries are selected. UNION is comparatively slow. UNION has slower performance because it has to remove duplicates using a different sort. The tables that UNION needs to select results from, the number of columns must be the same.

What is UNION ALL?

The UNION ALL command is used to select the results of two queries that have the same number of columns. It also selects duplicate results. UNION ALL is faster but may seem low due to the number of duplicates that will be returned. The tables whose results are needed to be selected by UNION ALL, the number of columns must be the same.

Key differences

1. UNION ALL selects the duplicate result but UNION does not select the duplicate result.
2. UNION ALL is faster than UNION.
3. UNION ALL seems slower than UNION due to the number of duplicate results that will be returned.
4. UNION ALL is preferable to UNION for faster results.
5. UNION and UNION ALL can only select queries from the same table or from two of the same tables.
6. The tables from which the results are needed to be selected by UNION ALL or UNION, the number of columns must be the same.
7. Network bandwidth usage and performance is different in both UNION and UNION ALL.
8. UNION is slower than UNION ALL because it has to remove duplicates using a different sort.