Technology

Difference Between UNION and UNION ALL

ADVERTISEMENT

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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add difference.wiki to your ad blocking whitelist or disable your adblocking software.

×