MySQL Union Operator

MySQL union clause is used to combine two or more result sets into a single result set.

MySQL Union syntax

The Basic syntax of Union is

SELECT <colum-names>
UNION [DISTINCT | ALL]
SELECT <colum-names>
....

There are a few basic rules to follow for Union operator:

• The number and order of columns in the select clauses need to match.
• The data types of the columns should match as well.

Union operator removes duplicates by default, So Distinct is applied by default. In order to allow duplicates, we need to use all.

MySQL Union Query

An example of the Union query is as follows;

select first_name, last_name from actor
UNION
select first_name, last_name from customer;

This query will automatically remove any duplicates. We will see the output of the Union query along with the output of the Union All to understand the difference.

MySQL 'Union All' Query

On the other hand, the Union All Query will permit duplicates.

select first_name, last_name from actor
UNION ALL
select first_name, last_name from customer;

Observe the outputs of the queries side-by-side to see that Union All returns more rows.

Union Clause with Alias in MySQL

If we wish to use a column alias with Union we need to specify it explicitly in the first table’s select clause. Putting the alias in the second table’s select clause will not apply the alias to the resultset.

For example: observe the output of the two Queries,

select concat(first_name, ' , ' ,last_name) AS FULLNAME
from actor
UNION
select concat(first_name, ' , ' ,last_name)
from customer ;

AND

select concat(first_name, ' , ' ,last_name)
from actor
UNION
select concat(first_name, ' , ' ,last_name) AS FULLNAME
from customer ;

The resultset as in the records returned by the two queries are exactly the same. However, observe the title given to the resultSet.