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?

Want an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

 

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_NAME LAST_NAME
Teresa Hudson
Fred Montgomery
Lois Lawson
Alice Perry
Ralph Montgomery
Dorothy Armstrong
Fred Wallace
Joseph Bell
Lois Martinez
Robert Rice

This contains 10 records.

Now, we have our employee table.

SELECT first_name, last_name
FROM employee;
FIRST_NAME LAST_NAME
Norma Henry
Evelyn Gonzalez
Stephen Hudson
Bruce Mitchell
Nicholas Dixon
Fred Nichols
Barbara Morales
Ralph Anderson
Lois Butler
Johnny Adams
George Burton
Dorothy Cook
Teresa Bell
Anthony Rice
Joseph Berry
Alice Perry
Ralph Montgomery
Roy Lane
Kenneth Bennett
Theresa Lawson

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_NAME LAST_NAME
Alice Perry
Anthony Rice
Barbara Morales
Bruce Mitchell
Dorothy Armstrong
Dorothy Cook
Evelyn Gonzalez
Fred Montgomery
Fred Nichols
Fred Wallace
George Burton
Johnny Adams
Joseph Bell
Joseph Berry
Kenneth Bennett
Lois Butler
Lois Lawson
Lois Martinez
Nicholas Dixon
Norma Henry
Ralph Anderson
Ralph Montgomery
Robert Rice
Roy Lane
Stephen Hudson
Teresa Bell
Teresa Hudson
Theresa Lawson

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_NAME LAST_NAME
Teresa Hudson
Fred Montgomery
Lois Lawson
Alice Perry
Ralph Montgomery
Dorothy Armstrong
Fred Wallace
Joseph Bell
Lois Martinez
Robert Rice
Norma Henry
Evelyn Gonzalez
Stephen Hudson
Bruce Mitchell
Nicholas Dixon
Fred Nichols
Barbara Morales
Ralph Anderson
Lois Butler
Johnny Adams
George Burton
Dorothy Cook
Teresa Bell
Anthony Rice
Joseph Berry
Alice Perry
Ralph Montgomery
Roy Lane
Kenneth Bennett
Theresa Lawson

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 an Oracle SQL function cheat sheet for easy reference? Click here to get a PDF file that contains all of Oracle functions for you to reference or print out.

Improve Your Oracle SQL With My 10-Day Email Course

x