MySQL UNION – Comprehensive Tutorial With Union Examples

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

MySQL UNION

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.

mysql_union

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.

mysql_union_disjoint

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.

mysql_union_all

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:

nameage
Darren32
Abhishek28
Amit30
Steven40
Kartik20
Akash22
Ian26
Shirley19
Joana21

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:

nameage
Darren32
Abhishek28
Amit30
Steven40
Kartik20
Akash22
Ian26
Shirley19
Joana21
Kartik20

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:

nameage
Abhishek28
Kartik20
Akash22
Shirley19
Joana21

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:

nameage
Shirley19
Kartik20
Joana21
Akash22
Ian26
Abhishek28
Amit30
Darren32
Steven40

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:

ParameterUNIONUNION ALL
DefinitionIs equivalent to UNION DISTINCT - ignores duplicate rows of data while returning resultReturns all rows including duplicates
SyntaxSELECT {columnList} FROM {table1}
UNION
SELECT {columnList} FROM {table2}
SELECT {columnList} FROM {table1}
UNION ALL
SELECT {columnList} FROM {table2}
Data requirementsData that’s combined should have similar data types and should be fetched in the same order when fetched from multiple tablesSame 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:

UNIONJOIN
Combines data from multiple tablesCombines data from multiple tables
Doesn’t require any specific condition to collate or combine dataJOIN 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 setData 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 straightforwardJOINS 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.