What is the difference between UNION and UNION ALL?

sql union union-all
Brian G picture Brian G · Sep 8, 2008 · Viewed 1.4M times · Source

What is the difference between UNION and UNION ALL?

Answer

Jim Harte picture Jim Harte · Sep 8, 2008

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).

#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)

Related questions

How to execute UNION without sorting? (SQL)

UNION joins two results and remove duplicates, while UNION ALL does not remove duplicates. UNION also sort the final output. What I want is the UNION ALL without duplicates and without the sort. Is that possible? The reason for this …

Read More
Why would I want .union over .unionAll in Spark for SchemaRDDs?

I'm trying to wrap my head around these two functions in the Spark SQL documentation– def union(other: RDD[Row]): RDD[Row] Return the union of this RDD and another one. def unionAll(otherPlan: SchemaRDD): SchemaRDD Combines the tuples of …

Read More
Adding DISTINCT to a UNION query

How do I get distinct title.id's from this: SELECT Title.id, Title.title FROM titles as Title HAVING points > 0 UNION ALL SELECT Title.id, Title.title FROM titles as Title HAVING points > 1 There is more to the …

Read More