What is the difference between UNION and UNION ALL?

UNION and UNION ALL are SQL operations that are used to concatenate tables.

The two datasets we'll use to demonstrate how the two operations work
The two datasets we'll use to demonstrate how the two operations work

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 ALL
UNION ALL

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.

UNION
UNION

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
table1
table1
table2
table2

Let’s now implement the UNION and UNION ALL on these tables.

SELECT * from table1
UNION 
SELECT * from table2
UNION
UNION
UNION ALL
UNION ALL

Notice how the repeating F:12 is removed in UNION, but stays in UNION ALL.

Free Resources

Copyright ©2025 Educative, Inc. All rights reserved