What’s the difference between UNION and UNION ALL in Oracle SQL? There are a few. Learn what they are in this article.

UNION vs UNION ALLWhat Is UNION and UNION ALL?

First of all, let’s explain what they are.

UNION and UNION ALL are both “set operators”.

They are keywords you can use to combine two sets of data, or two queries.

They are different to joins, where joins combine data from two tables into the same query. Set operators, such as UNION and UNION ALL, combine two queries into one result set.

UNION and UNION ALL can be used in between the two queries.

For example, let’s say you wanted to get a list of all of the first_name and last_name values in both of the customer and employee tables.

You could have a query that looked like this:

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee;

You can also use UNION ALL in the same way:

SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee;

So, how can you compare UNION vs UNION ALL?

Difference Between UNION and UNION ALL

The major difference between UNION ALL and UNION in SQL is that UNION removes any duplicate results from the final result set, and UNION ALL does not. UNION performs a DISTINCT on the result set to remove duplicates.

To remember this, consider that “ALL” in UNION ALL means “show all records”.

Therefore, UNION ALL will almost always show more results, as it does not remove duplicate records.

As a result of this, UNION is often slower than UNION ALL, because there is an operation to remove duplicate values (a.k.a DISTINCT).

 

Example of UNION and UNION ALL With Duplicates

Let’s see an example of UNION and UNION ALL.

We have this customer table:

SELECT first_name, last_name
FROM customer;
FIRST_NAMELAST_NAME
TeresaHudson
FredMontgomery
LoisLawson
AlicePerry
RalphMontgomery
DorothyArmstrong
FredWallace
JosephBell
LoisMartinez
RobertRice

This contains 10 records.

Now, we have our employee table.

SELECT first_name, last_name
FROM employee;
FIRST_NAMELAST_NAME
NormaHenry
EvelynGonzalez
StephenHudson
BruceMitchell
NicholasDixon
FredNichols
BarbaraMorales
RalphAnderson
LoisButler
JohnnyAdams
GeorgeBurton
DorothyCook
TeresaBell
AnthonyRice
JosephBerry
AlicePerry
RalphMontgomery
RoyLane
KennethBennett
TheresaLawson

This contains 20 records.

Now, what if we want these values in the same result set?

Let’s try an example of UNION.

SELECT first_name, last_name
FROM customer
UNION
SELECT first_name, last_name
FROM employee
FIRST_NAMELAST_NAME
AlicePerry
AnthonyRice
BarbaraMorales
BruceMitchell
DorothyArmstrong
DorothyCook
EvelynGonzalez
FredMontgomery
FredNichols
FredWallace
GeorgeBurton
JohnnyAdams
JosephBell
JosephBerry
KennethBennett
LoisButler
LoisLawson
LoisMartinez
NicholasDixon
NormaHenry
RalphAnderson
RalphMontgomery
RobertRice
RoyLane
StephenHudson
TeresaBell
TeresaHudson
TheresaLawson

We can see there are 28 records, which is a little less than 30 (20 + 10). This is because some duplicates have been removed by UNION.

There are two or more records of the same first_name and last_name in the customer table, or two or more matching records in the customer and employee tables.

Now, let’s run the same query using a UNION ALL example.

SELECT first_name, last_name
FROM customer
UNION ALL
SELECT first_name, last_name
FROM employee;
FIRST_NAMELAST_NAME
TeresaHudson
FredMontgomery
LoisLawson
AlicePerry
RalphMontgomery
DorothyArmstrong
FredWallace
JosephBell
LoisMartinez
RobertRice
NormaHenry
EvelynGonzalez
StephenHudson
BruceMitchell
NicholasDixon
FredNichols
BarbaraMorales
RalphAnderson
LoisButler
JohnnyAdams
GeorgeBurton
DorothyCook
TeresaBell
AnthonyRice
JosephBerry
AlicePerry
RalphMontgomery
RoyLane
KennethBennett
TheresaLawson

We can see that there are 30 records, which is slightly higher than the UNION query. It also matches both tables (20 + 10).

This is because duplicates have not been removed. We can see that there are two records with the first name of Alice and a last name of Perry.

So, that’s the main difference between UNION and UNION ALL.

 

Performance of UNION vs UNION ALL

In almost all cases, UNION ALL will perform better than UNION.

This is because UNION performs a DISTINCT which is often a costly step to run in a query.

UNION ALL does not perform a distinct, so is usually faster.

So, if you don’t need to have unique rows in your result set, or if you’re sure the rows in the database or query are unique already, then use UNION ALL.

Want To Improve Your Oracle SQL?

Improve your Oracle SQL queries by signing up to my free 10-day Oracle Tips email course.

BONUS: Oracle SQL functions PDF cheat sheet

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit