Difference WikiTechnology

Difference Between UNION and UNION ALL

Main Difference

UNION and UNION ALL both are used for combining the Select queries results. UNION ALL records all the results including duplicates. UNION don’t record the duplicate results. UNION ALL is faster than UNION. In reporting and application UNION is better than UNION ALL as duplicate results are not suitable.

What is UNION?

UNION command is used to select the results from the two queries having same number of columns. It don’t select duplicate results but only distinct entries are selected. UNION is comparatively slow. UNION is slower in performance as it has to remove the duplicates by distinct sorting. The tables from whom the results are needed to be selected by UNION, the number of columns must be same.

ADVERTISEMENT

What is UNION ALL?

UNION ALL command is used to select the results from the two queries having same number of columns. It also selects the duplicate results. UNION ALL is faster but it may appear low due to number of its duplicates to be returned. The tables from whom the results are needed to be selected by either UNION ALL, the number of columns must be same.

Key Differences

  1. UNION ALL selects the duplicate result but UNION don’t select the duplicate result.
  2. UNION ALL is faster than UNION.
  3. UNION ALL appear slower than UNION due to its number of duplicate results to be returned.
  4. UNION ALL is preferable over UNION for faster results.
  5. UNION and UNION ALL can only select queries from same table or two same tables.
  6. The tables from whom the results are needed to be selected by either UNION ALL or UNION, the number of columns must be same.
  7. The bandwidth usage of networking and performance is different in both of the UNION and UNION ALL.
  8. UNION is slower than UNION ALL as it has to remove the duplicates by distinct sorting.
ADVERTISEMENT

Comparison Video

YouTube video
Harlon Moss

Harlon currently works as a quality moderator and content writer for Difference Wiki. He graduated from the University of California in 2010 with a degree in Computer Science. Follow him on Twitter @HarlonMoss