# What is the difference between UNION and UNION ALL in SQL?

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

Course Preview