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:

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.

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”).

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to concatenate multiple rows into one field in MySQL?
2. How to efficiently convert rows to columns in SQL?
3. How to find duplicate values in SQL?
4. How to select first row in each GROUP BY group?