Trusted answers to developer questions
Trusted Answers to Developer Questions

Related Tags

union
union all
sql

What is the difference between UNION and UNION ALL?

Educative Answers Team

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

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

svg viewer
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.

svg viewer
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.

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

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

SELECT * from table1
UNION 
SELECT * from table2
svg viewer
UNION
svg viewer
UNION ALL

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

RELATED TAGS

union
union all
sql
Copyright ©2022 Educative, Inc. All rights reserved

Grokking Modern System Design Interview for Engineers & Managers

Ace your System Design Interview and take your career to the next level. Learn to handle the design of applications like Netflix, Quora, Facebook, Uber, and many more in a 45-min interview. Learn the RESHADED framework for architecting web-scale applications by determining requirements, constraints, and assumptions before diving into a step-by-step design process.

Keep Exploring