Problem
Let’s understand the difference between UNION and UNION ALL in SQL with an example.
Input
Let’s create a table named fruits with columns id, name, and type. We will insert some rows into this table:
id | name | type |
---|---|---|
1 | Apple | Fresh |
2 | Banana | Fresh |
3 | Apple | Dried |
4 | Banana | Dried |
5 | Cherry | Fresh |
Try Hands-On: Fiddle
What is the difference between UNION and UNION ALL?
UNION: Combines the result of two or more SELECT statements but removes duplicates from the result set.
UNION ALL: Combines the result of two or more SELECT statements and does not remove any duplicates from the result set.
UNION Result
Using UNION
(SELECT name FROM fruits WHERE type = 'Fresh')
UNION
(SELECT name FROM fruits WHERE type = 'Dried');
Result:
This will display the fruits that are both “Fresh” and “Dried”, but it will remove duplicates. Hence, “Apple” and “Banana” will each appear only once.
name |
---|
Apple |
Banana |
Cherry |
UNION ALL Result
Using UNION ALL
(SELECT name FROM fruits WHERE type = 'Fresh')
UNION ALL
(SELECT name FROM fruits WHERE type = 'Dried');
Result:
This will display the fruits that are both “Fresh” and “Dried”, but it will keep duplicates. Therefore, “Apple” and “Banana” will each appear twice (once for “Fresh” and once for “Dried”).
name |
---|
Apple |
Banana |
Cherry |
Apple |
Banana |