MySQL Union vs Union All

Difference Between MySQL Union vs Union All

Mysql union and union all operators are used to get the combined result set two or more tables which for two subqueries which involve the use of select clause to retrieve the same number and type of columns from both the subqueries. Both union and union all clauses have the same requirement and purpose but just with some of the difference in the way in which both of them will retrieve the final result set.

In this article, we will study the syntax and usage of union and union all clauses along with the similarities and differences that exist between both of them. We will also learn about the implementation of union and union all in mysql along with the help of an example.

Syntax of Union clause:

The syntax of the union clause in mysql is as shown below:

First select query
UNION
Second select query

The working of the union operator can be understood from the below Venn diagram example where one of the data set retrieved from the first subquery gives the output a,b,c while the dataset retrieved from the other query is p,c,r and when both these queries are combined with union operator in between the final resultset gives only one occurrence of c which is duplicated in both the resultset. Hence, it can be said that the union operator only combines the result and retrieves the unique values.

Example of Union Operator

There are two tables considered here namely employee_details which stores the data related to the employees and the other one is customer_details which is kept for storing the contact related information in the table. Both this table contains the names of the employee and the employee id. Let us see what are the contents of each of the table firstly –

SELECT * FROM [employee_details]

SELECT * FROM [contact_details]

If we perform union on both the tables using the following query only unique result set data values are retrieved combining data of both the tables as shown below –

SELECT employee_id, f_name FROM employee_details
UNION
SELECT employee_id, employee_name FROM contact_details
ORDER BY employee_id;

The output of the above query statement is as shown below –

Syntax of Union All clause –

The syntax of the union clause in mysql is as shown below –

First select query
UNION ALL
Second select query

When the same example is considered and in between the two queries union all operator is applied then it gives the output of elements a,b,c,p,c,r which contains a duplicate occurrence of c value as it is present in both the data sets. Hence, the union combines the result but also retrieves the duplicate values.

Example of Union All Operator

One of the examples where we have implemented union operator is as shown below –

SELECT employee_id, f_name FROM employee_details
UNION ALL
SELECT employee_id, employee_name FROM contact_details
ORDER BY employee_id;

The output of above query statement is as shown below –

The records of employee id with 101 and 103 have the same employee_id in both the tables as well as the same f_name column value, that’s why both the occurrences of it is persisted in the output of the union all operator.

Head to Head Comparison between MySQL Union vs Union All (Infographics)

Below are the top 8 differences between MySQL Union vs Union All:

Key Differences between MySQL Union vs Union All

Some of the key differences between MySQL Union vs Union All are:

The most important and the key feature which needs to be pointed out over here is that in case of union operator while combining the data from two sources, it eliminates the duplicate entries retrieved from both the datasets and keeps only one of its entry. In the case of union all operator, none of the elimination is performed and the duplicate values are still kept in the final result set as it is.

MySQL Union vs Union All Comparison Table

Let us discuss the top comparison between MySQL Union vs Union All:

Union	Union All
The UNION operator when applied on the two subqueries retrieving a particular data set combines them and returns the final result set containing only unique occurrences of data retrieved from both the tables.	The UNION ALL operator when applied on the two subqueries retrieving a particular data set combine them and return the final result set which may contain any occurrences of data values retrieved from both the tables.
The default behavior of the UNION operator involves the elimination of all the duplicate values from the combined data set retrieved from both datasets.	There is no such default behavior in the case of the UNION ALL operator.
The working of the UNION operator is much slower because after combining the data from the two tables, it also has one more step to perform which is the elimination of all the duplicate values.	The execution of the UNION ALL operator is comparatively faster because it only carries out a combination of two data sets.
Database designers and users prefer to use union operator as it retrieves unique results. Most frequently used as compared to UNION ALL.	Not much preferred by database designers and users. Though it depends upon the requirement of which operator will be used.
The syntax of the UNION operator is – sub query UNION sub query	The syntax of the UNION ALL operator is – sub query UNION ALL sub query
Venn diagram for representing the working of union operator is as shown below – The c data value is present in both the data sets, hence, only one occurrence of it is considered for the final result.	Venn diagram for representing the working of union all operator for same data sets is as shown below – Even though c is present in both data sets, both the occurrences are retained in the final result set.
One of the examples where we have implemented union operator is as shown below – SELECT employee_id, f_name FROM employee_details UNION SELECT employee_id, employee_name FROM contact_details ORDER BY employee_id; The output of the above query statement is as shown below – Even though the records of employee id with 101 and 103 have the same employee_id in both the tables as well as the same f_name column value, still only one of the occurrences of it is persisted in the output of the union operator.	One of the examples where we have implemented union operator is as shown below – SELECT employee_id, f_name FROM employee_details UNION ALL SELECT employee_id, employee_name FROM contact_details ORDER BY employee_id; The output of the above query statement is as shown below – The records of employee id with 101 and 103 have the same employee_id in both the tables as well as the same f_name column value, that’s why both the occurrences of it is persisted in the output of the union all operator.

Conclusion

The union operator and union operator both do the same job of combining the result set. The only major difference in their working is that the union operator eliminates the duplicate entries from the final result set while the union all operator persists the duplicate entries as well.

Recommended Articles

This is a guide to MySQL Union vs Union All. Here we also discuss the MySQL Union vs Union All key differences with infographics and comparison table. You may also have a look at the following articles to learn more –

1. MySQL vs SQLite
2. Back End Programming Languages
3. MySQL Table Dump
4. SQLite Data Types