What is the difference between UNION and UNION ALL?
What is the difference between
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).
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)
Read more... Read less...
Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.
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.
Example: If we have two tables, 1) Employee and 2) Customer
- Employee table data:
- Customer table data:
- UNION Example (It removes all duplicate records):
- UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):
UNION removes duplicates, whereas
UNION ALL does not.
In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION, depending on the volume of data being sorted, and the settings of various RDBMS parameters ( For Oracle
Basically, the sort is faster if it can be carried out in memory, but the same caveat about the volume of data applies.
Of course, if you need data returned without duplicates then you must use UNION, depending on the source of your data.
I would have commented on the first post to qualify the "is much less performant" comment, but have insufficient reputation (points) to do so.
In ORACLE: UNION does not support BLOB (or CLOB) column types, UNION ALL does.
The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.
You can avoid duplicates and still run much faster than UNION DISTINCT (which is actually same as UNION) by running query like this:
SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X
AND a!=X part. This is much faster then UNION.