How to Use UNION to Append Result Sets in MySQL

Using UNION to Append Result Sets

UNION is a SQL operator that appends rows from one result set to another. Union is often used to retrieve data from partitioned tables. For example, you have two tables where table sales06 stores 2006 sales data and table sales07 stores 2007 sales data. If you need to have one result set that holds both 2006 and 2007 sales data, you can use union to do it.

Here is a standard format of UINON query:

select_statement_1
UNION [ALL]
select_statement_2
UNION [ALL]
select_statement_3
...
...

Here are some rules that govern the way UNION operator is used in a query.

In a UNION query, there are at least two SELECT statements.
The two SELECT statements must have the same number of columns and the the columns must have compatible data types. See Practice #1.
The column headings in each of the SELECT statements do not have to have the same name. The column headings in the result of a UNION query are always taken from the first SELECT statement. See Practice #2.
If you want to sort the result set of the UNION operation, you can only put an ORDER BY clause after the last SELECT statement. ORDER BY clause can't be specified in any other SELECT statements in the UNION query. See Practice #3.
The column(s) used in ORDER BY clause can only be taken from the first SELECT statement. See Practice #3.
If you don't specify an ORDER BY clause in the UNION query, the result set is always sorted by the first column. See Practice #1.
If you use UNION ALL, the entire result set from the second SELECT statement is appended to the first SELECT statement. In this case, there could be duplicate records in the unioned result set. See Practice #4.
If you only use UNION, MySQL removes duplicate rows from the final result set. See Practice #5.

Practice #1: Using UNION All.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
This query unions customers table with employees table 
for common data. The first column identifies what info a 
row is related to - customer or employee.
 
Note the following facts about this query:
 
1. The first SELECT statement and the second SELECT 
statement both have four columns and each column 
has the same data type.
 
2. ORDER BY is not used in the query and the result set
is ordered by using the first column.
 
3. UNINON ALL is used so the entire result from the second 
SELECT statement is appended to the first SELECT statement.
*/
select 'Customer' as Type,
       ContactName as Name, 
       City, 
       PostalCode
from customers
union all
select 'Employee' as Type,
       concat(FirstName, ' ', LastName) as Name,
       City, PostalCode
from employees;

Query result set - 100 rows returned:
Using UNION All

Practice #2: Using UNION All with different column name.

/*
This query is the same as the one in Practice #1 except
that in the first SELECT statement, the column heading 
for the second column is changed to Full_Name. The second
SELECT statement still uses alias called Name for the
second column.
 
This query demonstrates that the column headings in the 
result of a UNION query are always taken from the first 
SELECT statement.
*/
select 'Customer' as Type,
       ContactName as Full_Name, 
       City, 
       PostalCode
from customers
union all
select 'Employee' as Type,
       concat(FirstName, ' ', LastName) as Name,
       City, PostalCode
from employees;

Query result set - 100 rows returned:
Using UNION All with different column name

Practice #3: Using UNION All with sorted result.

/*
This query is the same as the one in Practice #2 except
that it adds an ORDER BY clause at the end of the query.
 
The ORDER BY clause must be put at the very end of the 
UNION query and the column(s) used in ORDER BY clause must 
be taken from the first SELECT statement.
*/
select 'Customer' as Type,
       ContactName as Full_Name, 
       City, 
       PostalCode
from customers
union all
select 'Employee' as Type,
       concat(FirstName, ' ', LastName) as Name,
       City, PostalCode
from employees
order by Full_Name;

Query result set - 100 rows returned:
Using UNION All with sorted result

Practice #4: Using UNION All with duplicate rows in the result.

/*
This query unions City and Country data from customers 
table and suppliers table. Because UNION ALL is used, 
there are some duplicate rows returned in the result 
(e.g. Berlin and Germany).
 
Total 120 rows returned.
*/
select City, Country from customers
union all
select City, Country from suppliers
order by City;

Query result set - 120 rows returned:
Using UNION All with duplicate rows in the result

Practice #5: Using UNION to return unique rows in the result.

/*
This query is the same as the one in Practice #4
except that it uses UNION rather than UNION ALL.
 
UNION eliminates duplicates rows in the result set.
 
Total 93 rows returned.
*/
select City, Country from customers
union
select City, Country from suppliers
order by City;

Query result set - 93 unique rows returned:
Using UNION to return unique rows in the result

Practice #6: Using UNION for three tables.

/*
This query unions three tables.
 
Total 95 rows returned.
*/
select City, Country from customers
union
select City, Country from suppliers
union
select City, Country from employees
order by City;

Query result set - 95 unique rows returned:
Using UNION for three tables

Other tutorials in this category

1. Using Inner Joins to Combine Data from Two Tables

2. Using Outer Joins to Combine Data from Two Tables

3. Using Self Joins to Combine Data from the Same Table

Back to Tutorial Index Page

	Custom Search