by BehindJava

What is the difference between UNION and UNION ALL

Home » interview » What is the difference between UNION and UNION ALL

In this blog, we are going to learn about the difference between UNION and UNION ALL.

UNION vs UNION ALL

  • UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
  • There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).
  • To identify duplicates, records must be comparable types as well as compatible types. This will depend on the SQL system. For example the system may truncate all long text fields to make short text fields for comparison (MS Jet), or may refuse to compare binary fields (ORACLE).

    • UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
    • UNION ALL does not remove duplicates, and it therefore faster than UNION.

Note: While using this commands all selected columns need to be of the same data type.

UNION Example:

SELECT 'foo' AS bar UNION SELECT 'foo' AS bar

+-----+
| bar |
+-----+
| foo |
+-----+
1 row in set (0.00 sec)

UNION ALL example:

SELECT 'foo' AS bar UNION ALL SELECT 'foo' AS bar

+-----+
| bar |
+-----+
| foo |
| foo |
+-----+
2 rows in set (0.00 sec)