MySQL UNION Operator and comparison to UNION ALL
This article is all about MySQL UNION Operator and a guide on how to efficiently use this operator. Firstly, I would like to give an introduction about Union Operator in MySQL, then will proceed further.
If you are new to SQL queries, firstly please read this article on SQL basics.
What is a UNION Operator?
Union operator combines the results from one or more SELECT statements into an integrated result set. The result we get will be a combination of all the SELECT statements. These Select statements are concatenated with the keyword UNION. By default, all the records fetched are unique as this statement applies the DISTINCT operator by default.
Syntax of MySQL UNION Operator
|
1 2 3 4 5 6 7 8 |
SELECT column_one, column_two, ... column_n FROM table1 UNION SELECT column_one, column_two, ... column_n FROM table2 UNION . . . SELECT column_one, column_two, ... column_n FROM table_n; |
- MySQL CASE In WHERE CLAUSE: Conditional Conditions
- How To Export MySQL Database Using Command Prompt Or Batch File
- MySQL Interview Questions Frequently Asked
UNION Operator Rules
There are a few rules to be followed in using the UNION operator. These points are required while using this statement to fetch data from the database.
- All the SELECT statements combined to form a UNION must have an equal number of columns.
- All the columns in each SELECT statement must be in the same sequence.
- Each column at its respective position must have similar data types.
Let’s see the sample data and do some operations.
Sample Data of first table ‘arts_students ‘.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE TABLE arts_students ( student_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, student_name varchar(255) NOT NULL, student_email varchar(100) DEFAULT NULL, student_dob date DEFAULT NULL, student_subject varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO arts_students (student_id, student_name, student_email, student_dob, student_subject) VALUES (1, 'Oliver', 'oliver@techbriefers.com', '2000-05-06', 'Sociology'), (2, 'Jack', 'jack@techbriefers.com', '2000-03-01', 'Geography'), (3, 'Harry', 'harry@techbriefers.com', '0199-12-11', 'English'), (4, 'William', 'william@techbriefers.com', '2000-03-24', 'Philosophy'), (5, 'Abby', 'abby@techbriefers.com', '2000-09-11', 'Arts'), (6, 'Nicolas', 'nic@techbriefers.com', '1999-09-11', 'Philosophy'); |
Sample Data of second table ‘science_students’.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE TABLE science_students ( student_id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, student_name varchar(255) NOT NULL, student_email varchar(100) DEFAULT NULL, student_dob date DEFAULT NULL, student_subject varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO science_students (student_id, student_name, student_email, student_dob, student_subject) VALUES (1, 'John', 'john@techbriefers.com', '2000-05-01', 'Maths'), (2, 'Suzan', 'suzan@techbriefers.com', '2000-02-01', 'Physics '), (3, 'Nikki', 'nikki@techbriefers.com', '1999-12-11', 'Chemistry '), (4, 'Mathew', 'mathew@techbriefers.com', '2000-03-03', 'Biology '), (5, 'Nicolas', 'nic@techbriefers.com', '1999-09-11', 'Pedagogy'); |
Performing a simple UNION operation
Here, I am performing a simple UNION operation on both tables. Both the tables have exactly the same columns in the same sequence. Hence, there will not be an issue will the below query.
|
1 2 3 |
SELECT * FROM `arts_students` UNION SELECT * FROM `science_students` |
The result will be:
Selecting all columns using * worked here because of the only reason that both tables have the same column in the same sequence. This is not a good practice and we always should use column names to fetch data in UNION.
|
1 2 3 |
SELECT `student_name`, `student_email` FROM `arts_students` UNION SELECT `student_name`, `student_email` FROM `science_students` |
- Close Div By Close Button Click Or Body Click By JQuery
- How To Extend Group_concat() Limit In MySQL & PhpMyAdmin
- Detect a page refresh or new page entered in PHP
Using the order clause in UNION
To use order clause, it is mandatory to mention only those columns which are used in the SELECT statement. In the code below, we can use only student_name and student_email to use in the ORDER clause.
|
1 2 3 4 |
/* In Ascending order of student Name */ SELECT `student_name`, `student_email` FROM `arts_students` UNION SELECT `student_name`, `student_email` FROM `science_students` ORDER BY student_name ASC; |
|
1 2 3 |
SELECT `student_name`, `student_email` FROM `arts_students` UNION SELECT `student_name`, `student_email` FROM `science_students` ORDER BY student_email ASC; |
How to use a column alias in UNION operation
Aliases in SQL/ MySQL are used to give a temporary name to a table, or a column in a table using AS. If you have used aliasing for columns, the ORDER BY clause must use that alias to sort data. Using original column names will create error as those will be unknown for the final statement.
|
1 2 3 |
SELECT `student_name` AS name, `student_email` AS email FROM `arts_students` UNION SELECT `student_name`, `student_email` FROM `science_students` ORDER BY name ASC; |
In the case of using AS in UNION operation, we need to alias the columns of the first SELECT statement only. Because alias of preceding statements will not be considered, ie. neglected.
Furthermore, I will explain more how to use the UNION operator and will issues when something goes wrong. But, before that let’s have a look at UNION ALL operator.
MySQL UNION ALL Operator
MySQL UNION ALL Operator also combines multiple SELECT statements. However, differs from UNION Operator in a way that it does not remove duplicate records. If there is any data which is duplicate, then two similar rows will be fetched in this case, unlike in UNION that applies DISTINCT clause by default.
UNION vs UNION ALL
Firstly I am fetching data using column names (student_name, student_email) with the UNION operator which will give unique results for the combination of data stored in both the tables.
|
1 2 3 |
SELECT `student_name`, `student_email` FROM `arts_students` UNION SELECT `student_name`, `student_email` FROM `science_students` ORDER BY student_name; |
Now, taking the same columns to fetch data from the same tables. But this time I am using UNION ALL operator. This will also result in the combination of records of both the tables but if data is duplicated, it will not be removed.
|
1 2 3 |
SELECT `student_name`, `student_email` FROM `arts_students` UNION ALL SELECT `student_name`, `student_email` FROM `science_students` ORDER BY student_name; |
In this case, student_name Nicolas with student_email ‘nic@techbriefers.com‘ is present in both the tables. When we do we will combine both records, this row will come twice. UNION operator removes the redundancy whereas UNION ALL Doesn’t. Hence we get one row more while using UNION ALL.
What if the sequence is disturbed?
As I told before, the column in all SELECT statements must be in the same sequence. If not, the query will give priority to the column names of the first SELECT statement. Furthermore, statements will fetch data in the sequence they are calling data. But the final result will be affected. This can be understood by the example given below.
|
1 2 3 |
SELECT `student_name`, `student_email` FROM `arts_students` UNION ALL SELECT `student_email`, `student_name` FROM `science_students`; |
In the above result, data from the first table is correctly fetched but data from the second table is shown according to the column sequence of the first statement.
What if the number of columns not equal in UNION operation?
If the number of columns will not be equal in all the statements, it will create an error. Below is the example to demonstrate this.
|
1 2 3 |
SELECT `student_name`, `student_email` FROM `arts_students` UNION ALL SELECT `student_email`, `student_name`, `student_id` FROM `science_students` |
#1222 – The used SELECT statements have a different number of columns