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 | UNION ALL |
|---|---|
| It combines the result set from multiple tables and returns distinct records into a single result set. | It combines the result set from multiple tables and returns all records into a single result set. |
| Following is the basic syntax of UNION operator: SELECT column_list FROM table1 UNION SELECT column_list FROM table2; | Following is the basic syntax of UNION ALL operator: SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2; |
| It has a default feature to eliminate the duplicate rows from the output. | It has no feature to eliminate the duplicate rows from the output. |
| Its performance is slow because it takes time to find and then remove duplicate records. | Its performance is fast because it does not eliminate the duplicate rows. |
| Most database users prefer to use this operator. | Most database users do not prefer to use this operator. |
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.
