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".
About the Author
You 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
MySQL: Delete Data from Table - Difference between TRUNCATE, DELETE and DROP
Tutorial | 0 Comments
SQLite: Creating an Index on one or more Columns
Info | 0 Comments
MySQL: Add new Column to a Table
Tutorial | 0 Comments
MySQL: Write current Date or Time into Column
Tutorial | 0 Comments
Micro SD Memory Cards: Difference between microSD, microSDHC, microSDXC and microSDUC
Info | 12 Comments
List of all previous European Football Champions and Runners-Up
Info | 0 Comments
MySQL: 3 Ways of showing Table Definition and Structure
Info | 0 Comments
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.
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.
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:
This is our result:
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:
Now, we get the following result:
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":
However, this time, we get the same result for both queries:
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