00 Votes

SQL: Difference between UNION and UNION ALL

Info by Stefan Trost | Last update on 2022-12-29 | Created on 2016-07-23

What is the difference between combining a SQL query with UNION compared to using UNION ALL in that case? In this article I would like to investigate this question and demonstrate my explanations using some examples.

The general difference is that UNION removes all duplicates while UNION ALL is not doing so.

Accordingly, UNION ALL is fast are and has a better performance because the result do not have to be scanned for double records. A double record is a record in which all columns of the result have the same content.

Example

Let's take a look at an example first. Assumed we have two tables, let us name them team1 and team2.

Table "team1"
firstname  points
Anna       10
Thomas     12
Marry      8

Table "team2"
firstname  points
Anna       7
John       10
Claus      2

In this table, you can find a name (column "firstname") and a number of points (column "points") of some team members (normally, of course, we should take another table layout in that case having all teams in one table, but for that example it should be okay).

UNION ALL

First, we want to eat out all names from both tables with "UNION ALL". For that, we are using the following query:

SELECT firstname FROM team1
UNION ALL
SELECT firstname FROM team2

This is our result:

Anna
Thomas
Marry
Anna
John
Claus

As you can see, all names will be listed, the name "Anna" appears in the list for two times.

UNION

Next, we want to do the same query using "UNION" instead:

SELECT firstname FROM team1
UNION
SELECT firstname FROM team2

Now, we get the following result:

Anna
Thomas
Marry
John
Claus

Again, all names are read out. However, this time, the duplicates are removed from the result so that the name "Anna" is now appearing only for one time.

If all records are different

In the next example, we not only want to read out the name, but also the points. We are using the following queries for "UNION" and "UNION ALL":

SELECT firstname, points FROM team1
UNION ALL
SELECT firstname, points FROM team2
SELECT firstname, points FROM team1
UNION
SELECT firstname, points FROM team2

However, this time, we get the same result for both queries:

Anna     10
Thomas   12
Marry    8
Anna     7
John     10
Claus    2

Because the data records "Anna - 10" and "Anna - 7" are different, both of them will be listed in the results. If "Anna" would have the same number of points in both lines, again, one of the entries would have been sorted out as "double".

ReplyPositiveNegative
2Best Answer2 Votes

ALT!!

The difference is that UNION removes all duplicates while UNION ALL is not doing so.

Accordingly, UNION ALL is fast are and has a better performance because the result do not have to be scanned for double records. A double record is a record in which all columns of the result have the same content.

Example

Assumed we have two tables, let us name them team1 and team2.

Table "team1"
firstname  points
Anna       10
Thomas     12
Marry      8

Table "team2"
firstname  points
Anna       7
John       10
Claus      2

In this table, you can find a name (column "firstname") and a number of points (column "points") of some team members (normally, of course, we should take another table layout in that case having all teams in one table, but for that example it should be okay).

UNION ALL

First, we want to eat out all names from both tables with "UNION ALL". For that, we are using the following query:

SELECT firstname FROM team1
UNION ALL
SELECT firstname FROM team2

This is our result:

Anna
Thomas
Marry
Anna
John
Claus

As you can see, all names will be listed, the name "Anna" appears in the list for two times.

UNION

Next, we want to do the same query using "UNION" instead:

SELECT firstname FROM team1
UNION
SELECT firstname FROM team2

Now, we get the following result:

Anna
Thomas
Marry
John
Claus

Again, all names are read out. However, this time, the duplicates are removed from the result so that the name "Anna" is now appearing only for one time.

If all records are different

In the next example, we not only want to read out the name, but also the points. We are using the following queries for "UNION" and "UNION ALL":

SELECT firstname, points FROM team1
UNION ALL
SELECT firstname, points FROM team2
SELECT firstname, points FROM team1
UNION
SELECT firstname, points FROM team2

However, this time, we get the same result for both queries:

Anna     10
Thomas   12
Marry    8
Anna     7
John     10
Claus    2

Because the data records "Anna - 10" and "Anna - 7" are different, both of them will be listed in the results. If "Anna" would have the same number of points in both lines, again, one of the entries would have been sorted out as "double".
2016-07-23 at 22:09

ReplyPositive Negative
Reply

About the Author

AvatarYou can find Software by Stefan Trost on sttmedia.com. Do you need an individual software solution according to your needs? - sttmedia.com/contact
Show Profile

 

Related Topics

Important Note

Please note: The contributions published on askingbox.com are contributions of users and should not substitute professional advice. They are not verified by independents and do not necessarily reflect the opinion of askingbox.com. Learn more.

Participate

Ask your own question or write your own article on askingbox.com. That’s how it’s done.