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.

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

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

Sample Data of second table ‘science_students’.

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.

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.

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.

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.

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.

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.

In this case, student_name Nicolas with student_email ‘[email protected]‘ 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.

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.

#1222 – The used SELECT statements have a different number of columns

Leave a Reply