This tutorial explains the MySQL UNION command, its types, Union vs Union All, and examples to combine data from 2 or more queries:
MySQL UNION is used to combine results from multiple SELECT queries into a single result set. It’s also possible to have all the complex queries/operations in the SELECT queries and perform UNION with other SELECT statements to yield a combined result.
The default characteristic of a UNION statement would be to remove the duplicate entries or rows from the resulting set of rows, however, it does provide ways to fetch the duplicate records as well using the UNION ALL clause.
=> Click here for the complete MySQL tutorial series
What You Will Learn:
Understanding UNION Through Set Theory
UNION operation could be very well understood from the principles of SET Theory.
Suppose we have tables A & B as represented by sets below and they have some overlapping data (or it can be totally unrelated too) – UNION will include the combination of data from both the tables.
Let’s see an example where sets A & B have some common elements. The UNION will contain all the values from set A & B with the duplicate values omitted.
Now, what if sets A & B are disjoint and don’t contain any common elements? Here as well, the UNION will return the same result.
Let’s discuss the scenario where there are overlapping elements between sets and we want to have the duplicate values also to appear in the result set.
MySQL provides a way to do that using the UNION ALL option as shown in the below image.
What Problem Does MySQL UNION Solve
MySQL UNION is used when you have similar data across 2 or more tables and you want to see a combined view of the data contained in both the tables rather than running SELECT statements for individual tables.
For example – Suppose there are 2 tables – Employee and Student. And you are working on a person database which just wants to have a name, age, and birth date for all the employees and students.
Without UNION, you will need to execute separate SELECT queries for both the tables and then perform the desired computation with the obtained result set.
MySQL UNION Syntax
The below queries will return a UNION of 2 or more than 2 SELECT statements.
SELECT {column1}, {column2} FROM {table1}
UNION [ALL | DISTINCT]
SELECT {column3}, {column4} FROM {table2}
UNION [ALL | DISTINCT]
SELECT ...
Let’s try to see different components of the syntax
- We can see that it is possible to combine multiple SELECT queries with UNION to get the resulting row set.
- Column name and data types: It’s important to understand that the columns that we want to combine should have the same or compatible data type. For example: if you have column1 as STRING, then column3 should be STRING as well (or STRING compatible).
- The column names and positions are determined from the first SELECT statement in the UNION query. For example, in the above syntax: column1 and column2 are named as header columns in the result set and the values of column3 and column4 are mapped to column1 and column2, respectively.
- The results of a UNION query by default removes duplicate entries. For example, in this case, if there’s a duplicate entry that has an exact match and has the same values for column1 and column2 then those rows will be omitted from the result set.
If duplicates are desired, then we can use the ‘ALL’ option along with the UNION.
The usage of DISTINCT is implied by default. Please note that it could be specified explicitly as well to have more readability.
Let’s see a sample example of a UNION query.
Suppose there are 2 tables – employee and student – each having personal information.
CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(100), age INT, dob DATE, department VARCHAR(100)); CREATE TABLE student (id INT PRIMARY KEY, name VARCHAR(100), age INT, dob DATE, department VARCHAR(100));
Now, enter some dummy data into both of these tables as shown below:
INSERT INTO employee values (1,'Darren', 32, '1988-05-20', 'ENGINEERING'), (2,'Abhishek', 28, '1992-05-20', 'ACCOUNTING'), (3,'Amit', 30, '1990-09-20', 'ENGINEERING'), (4,'Steven', 40, '1980-05-21', 'HUMAN RESOURCES'), (5,'Kartik', 20, '2000-05-12', 'TRAINEE'); ---------------------------------------------- INSERT INTO student values (1,'Akash', 22, '1998-05-17', 'COMPUTER'), (2,'Ian', 26, '1994-06-18', 'COMPUTER'), (3,'Shirley', 19, '2001-11-20', 'MECHANICAL'), (4,'Joana', 21, '1999-05-21', 'ELECTRONICS'), (5,'Kartik', 20, '2000-05-12', 'COMPUTER');
As you can see above, we have intentionally added a row that has the same attributes for name, age, and date of birth.
Let’s now see how we can combine the data in these 2 tables using UNION commands. We will be querying user name and age from both the tables.
Simple UNION
Query:
SELECT name, age FROM employee UNION SELECT name, age FROM student;
You will see 9 records in the result (which means that the above query has omitted the duplicate entry).
Output:
| name | age |
|---|---|
| Darren | 32 |
| Abhishek | 28 |
| Amit | 30 |
| Steven | 40 |
| Kartik | 20 |
| Akash | 22 |
| Ian | 26 |
| Shirley | 19 |
| Joana | 21 |
UNION With UNION ALL
Using UNION with ALL clause will ensure that the duplicate entries get displayed as well.
Query:
SELECT name, age FROM employee UNION ALL SELECT name, age FROM student;
Output:
| name | age |
|---|---|
| Darren | 32 |
| Abhishek | 28 |
| Amit | 30 |
| Steven | 40 |
| Kartik | 20 |
| Akash | 22 |
| Ian | 26 |
| Shirley | 19 |
| Joana | 21 |
| Kartik | 20 |
UNION With Condition
Let’s add conditions to the SELECT statements where we want data of the employees aged less than 30 and Students aged less than 25.
Query:
SELECT name, age FROM employee where age < 30 UNION SELECT name, age FROM student where age < 25;
Output:
| name | age |
|---|---|
| Abhishek | 28 |
| Kartik | 20 |
| Akash | 22 |
| Shirley | 19 |
| Joana | 21 |
As you can see above, the result set includes the combined result with individual SELECT conditions validated.
Ordering UNION Results
The Results of a UNION query are unordered by default.
In order to impose an ordering by a column existing in the resulting set, an ORDER BY clause can be specified at the end of the UNION command.
Let’s use the same Employee/Student data and order the UNION results in ascending order by age.
SELECT name, age FROM employee UNION SELECT name, age FROM student ORDER BY age asc
Output:
| name | age |
|---|---|
| Shirley | 19 |
| Kartik | 20 |
| Joana | 21 |
| Akash | 22 |
| Ian | 26 |
| Abhishek | 28 |
| Amit | 30 |
| Darren | 32 |
| Steven | 40 |
The above result set is ordered by age values in increasing order. We can also use column aliases to refer to the columns in the ORDER BY Clause.
For example: – A query like the below will also yield the same result.
SELECT name as customer_name, age as customer_age FROM employee UNION SELECT name, age FROM student ORDER BY customer_age asc
There’s another way to use the ORDER BY clause by just mentioning the column position instead of the column name.
For example: In the above UNION query, we are selecting name and age which implies that these columns are at positions 1 & 2, respectively.
So to ORDER BY age, we can simply specify the position instead of the actual column name.
Hence, the below query will yield the same result as well.
SELECT name, age FROM employee UNION SELECT name, age FROM student ORDER BY 2 asc
MySQL UNION Vs UNION ALL
MySQL provides 2 variants of UNION i.e. UNION DISTINCT and UNION ALL
Please note that the DISTINCT is implied by default, even if not stated.
The major difference between both of them is UNION ALL allows to combine and return duplicate rows as well, whereas UNION just returns the combined rows removing duplicates.
The below table explains the details:
| Parameter | UNION | UNION ALL |
|---|---|---|
| Definition | Is equivalent to UNION DISTINCT - ignores duplicate rows of data while returning result | Returns all rows including duplicates |
| Syntax | SELECT {columnList} FROM {table1} UNION SELECT {columnList} FROM {table2} | SELECT {columnList} FROM {table1} UNION ALL SELECT {columnList} FROM {table2} |
| Data requirements | Data that’s combined should have similar data types and should be fetched in the same order when fetched from multiple tables | Same as UNION |
Frequently Asked Questions
Q #1) Is Union faster than JOIN?
Answer: UNION and JOINs are used for practically 2 different purposes.
Technically speaking, UNION is way faster than JOIN (especially for large data sets) as UNION just concatenates rows of data from the individual SELECT statements.
Q #2) What is the UNION ALL operator?
Answer: Similar to UNION, the UNION ALL operator also returns the UNION between 2 SELECT queries, but the difference is that it also contains the duplicate rows/entries.
Q #3) What is the difference between UNION and JOIN?
Answer: Both UNION and JOIN are used to combine data from 2 or more tables. But there’s a huge difference in terms of the result set obtained and the way data is fetched.
Similarities and differences between JOIN and UNION is enlisted in the below table:
| UNION | JOIN |
|---|---|
| Combines data from multiple tables | Combines data from multiple tables |
| Doesn’t require any specific condition to collate or combine data | JOIN works on a JOIN condition which is required to validate data coming in result set |
| Data from different tables is taken as different rows of the result set | Data from different tables is combined into single row - for ex- a row in result set could contain 2 columns from table1, 3 columns from table 2 etc depending on the JOIN condition and SELECT query |
| UNIONs are simple and straightforward | JOINS require complex conditions and depending on needs multiple types of joins like INNER / OUTER etc could be used. |
Conclusion
In this tutorial, we have learned about using MySQL UNION to combine data from 2 or more SELECT statements.
The UNION statement is really helpful for collating similar data from large sets of different tables and then running an analysis on the combined data.
UNION command also supports an ALL clause which enables fetching the duplicate records as well.