MySQL Union & Union All

If you are interested to learn about the MySQL Window Function

MySQL Union is an operator that allows us to combine two or more results from multiple SELECT queries into a single result set. It comes with a default feature that removes the duplicate rows from the result set. MySQL always uses the name of the column in the first SELECT statement will be the column names of the result set(output). The MySQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.

MySQL Union must follow these basic rules:

• The number and order of the columns should be the same in all tables that you are going to use.
• The data type must be compatible with the corresponding positions of each select query.
• The column name selected in the different SELECT queries must be in the same order.

MySQL Union Syntax

The following are the syntax of Union operator in MySQL:

SELECT column_list FROM table1  
UNION  
SELECT column_list FROM table2;  

We can understand the Union operator with the following visual representation:

In the above image, we can see that the Union operator removes the duplicate rows and returns unique rows only.

Union vs. Join

The Union and Join clause are different because a union always combines the result set vertically while the join appends the output horizontally. We can understand it with the following visual representation:

MySQL Union Example

Let us create two tables and see how the Union operator works in MySQL.

Table: student1

Table: student2

The following statement returns a result set that contains student names and subjects by combining both tables. When you execute this statement, you will notice that if the student name and subject have the same field in both tables, then each one will be listed once. It is because the Union operator returns only the distinct values.

SELECT stud_name, subject FROM student1  
UNION  
SELECT stud_name, subject FROM student2;  

After executing the above statement, we will get the following output.

In the above output, you can see that the MySQL Union uses the heading of the column name of the result set the same as the column name of the first SELECT statement. Sometimes you want to change the heading of the column name of the output with a different heading. We can do this by using the column aliases explicitly in the first SELECT statement.

The following example explains it more clearly:

SELECT stud_name AS student_name, subject AS course FROM student1  
UNION  
SELECT stud_name, subject FROM student2;  

It will give the following output where the heading of the column name is changed from “stud_name” to “student_name” and “subject” to “course“, respectively.

MySQL Union with ORDER BY

If you want to sort the result returned from the query using the union operator, you need to use an ORDER BY clause in the last SELECT statement. We can put each SQL SELECT queryin the parenthesis and then use the ORDER BY clausein the last SELECT statement as shown in the following example:

(SELECT stud_name, subject, marks FROM students)  
UNION  
(SELECT stud_name, subject, marks FROM student2)  
ORDER BY marks;  

After the successful execution of the above statement, we will get the following output that sorts the student name and subject in ascending order according to the marks obtained:

MySQL Union All

This operator returns all rows by combining two or more results from multiple SELECT queries into a single result set. It does not remove the duplicate rows from the result set. We can understand it with the following pictorial representation:

The difference between Union and Union All operators is that “Union” returns all distinct rows (eliminate duplicate rows) from two or more tables into a single output. In contrast, “Union All” returns all the rows, including duplicates rows.

Syntax

The following are the syntax of Union operator in MySQL:

SELECT column_list FROM table1  
UNION ALL  
SELECT column_list FROM table2;  

Example

Let us take a table (student1 and student2) that we have created previously and understand how Union All operator works in MySQL. The following statement returns all student names, subjects, and marks, including all duplicate rows in a single result. It also sorts the student name in ascending order according to the marks obtained using the ORDER BY clause.

(SELECT stud_name, subject, marks FROM students)  
UNION ALL  
(SELECT stud_name, subject, marks FROM student2)  
ORDER BY marks;  

When you execute the above statement, you will get the following output that contains all duplicate rows present in the result set:

Union vs. Union All Operator

The following comparison table explains their main differences in a quick manner:

Union and Union All Example

Let us understand the differences between Union and Union All operators through an example. Suppose we have a table named “Student” and “Student2” that contains the following data:

Table: Student

Table: Student2

Following SQL statement returns the distinct name of cities from both tables using the UNION query:

SELECT City FROM student  
UNION  
SELECT City FROM student2  
ORDER BY City;  

After executing the above statement, we will get the below output because the Union operator returns only the distinct values.

Following SQL statement returns all cities name including duplicates from both tables using the UNION ALL query:

SELECT City FROM student  
UNION ALL  
SELECT City FROM student2  
ORDER BY City;  

After executing the above statement, we will get the below output because the Union All operator returns whole records without eliminating distinct values.