Difference Between Union and Union All

What is the difference between UNION and 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 Example:

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

Result:

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

UNION ALL example:

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

Result:

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

Union and Union all - result

A UNION statement effectively does a SELECT DISTINCT on the results set.

UNION

The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead, it just pulls all rows from all tables fitting your query specifics and combines them into a table.

UNION Example:

SELECT 'hello' AS world UNION SELECT 'hello' AS world ;
 world
-------
 hello
(1 row)

UNION ALL example:

SELECT 'hello' AS world UNION ALL SELECT 'hello' AS world ;
 world
-------
 hello
 hello
(2 rows)

In your case:

--Situation 2          UNION ALL      UNION          100

In situation 2, the UNION, will remove the duplicates from the first UNION ALL

That's the reason you are getting 100 records instead of 109.

SELECT 'hello' AS world UNION ALL SELECT 'hello' AS world UNION SELECT 'hello' AS world ;
 world
-------
 hello
(1 row)

If you still need all the rows, this is the way

SELECT 'hello' AS world UNION ALL (SELECT 'hello' AS world UNION SELECT 'hello1' AS world) ;
 world
--------
 hello
 hello
 hello1
(3 rows)

performance of union versus union all

UNION ALL will perform better than UNION when you're not concerned about eliminating duplicate records because you're avoiding an expensive distinct sort operation. See: SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

Performance of UNION vs UNION ALL

The mainly aspect is that UNION is shortcut for UNION DISTINCT and so

the difference in performance between UNION and UNION ALL are related to the

need to obtain a distinct result and for this the database engine and the query optimizer
are surely more effective and most efficient than the filtring alogoritm based on PHP code in application.

The dictinct Operation can, moreover, benefit from the pre-optimizations for group by functionality

Not only, the duplicate data filtering is generally based on ordered data
while the select sql functions work without explicit ordering,
and therefore the need for filtering data with the application can lead to less efficient and more longer queries.

Generally the db engine is much more efficient that application PHP functions code so the Option 1 is generally the better choise

SQL: In UNION vs UNION ALL, what is the implied ID?

There is no "join" with a union. Nor is there a concept of an "id".

The difference between union and union all is that union returns only unique records -- removing duplicates. All columns that participate in the union are considered as one unit (you may say that all the columns are "keys"). And, for the purposes of uniqueness, NULL values are considered the same.

---

Related Topics