Programming MySQL MySQL UNION Operator and comparison to UNION ALL

MySQL UNION Operator and comparison to UNION ALL

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.

Contents show

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

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;
  1. MySQL CASE In WHERE CLAUSE: Conditional Conditions
  2. How To Export MySQL Database Using Command Prompt Or Batch File
  3. 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.

  1. All the SELECT statements combined to form a UNION must have an equal number of columns.
  2. All the columns in each SELECT statement must be in the same sequence.
  3. 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 ‘.

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’. 

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');
Original Data from Both tables

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.

SELECT * FROM `arts_students` 
UNION 
SELECT * FROM `science_students`

The result will be:

Simple union query result on two tables

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.

SELECT  `student_name`, `student_email`  FROM `arts_students` 
UNION 
SELECT  `student_name`, `student_email`  FROM `science_students`

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.

/*  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;
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.

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;
MySQl Union operator column alias example

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.

MySQl Union operator alias ignored example

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.

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.

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. 

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. 

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

Share this article

Leave a Reply