What is the difference between UNION and UNION ALL?
UNION and UNION ALL are SQL operations that are used to concatenate tables.
UNION ALL
If we perform UNION ALL on the two datasets, the resulting data set will contain all the elements of both datasets combined. If a data point occurs multiple times, it is added every time.
UNION
If we take a UNION of two datasets, the resulting dataset will contain all the unique elements of both datasets combined. If a data point occurs multiple times, it will only be considered once. This is equivalent to taking UNION ALL and then DISTINCT over the resulting data set. Hence, UNION is slower than UNION ALL.
Code examples
Now let’s see how each of them is implemented in code. We’ll use the tables constructed below to demonstrate.
CREATE TABLE table1(id char,score int);INSERT INTO table1(id,score)VALUES('A', 10);INSERT INTO table1(id,score)VALUES('B', 11);INSERT INTO table1(id,score)VALUES('F', 12);SELECT * from table1
Let’s now implement the UNION and UNION ALL on these tables.
SELECT * from table1UNIONSELECT * from table2
Notice how the repeating F:12 is removed in UNION, but stays in UNION ALL.
Free Resources