» Basic MySQL Tutorial » Combining Result Sets by Using MySQL UNION

Combining Result Sets with MySQL UNION

Summary: In this tutorial, you will learn how to use MySQL UNION statement to combine two or more result sets from multiple SQL SELECT statements into a single result set.

Like SQL standard, MySQL UNION allows you to combine two or more result sets from multiple tables together. The syntax of using MySQL UNION is as follows:

SELECT statement
UNION [DISTINCT | ALL]
SELECT statement
UNION [DISTINCT | ALL]

…

In order to use UNION statement, there are some rules you need to follow:

  • The number of columns in each SELECT statement has to be the same .
  • The data type of the column in the column list of the SELECT statement must be the same or at least convertible.

By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.

If you use UNION ALL explicitly, the duplicate rows remain in the result set.  You only use this in the cases that you want to keep duplicate rows or you are sure that there is no duplicate rows in the result set.

Let’s practice with couples of examples with MySQL UNION to get a better understanding.

Suppose you want to combine customers and employees infomation into one result set, you use the following query:

SELECT customerNumber id, contactLastname name
FROM customers
UNION
SELECT employeeNumber id,firstname name
FROM employees

Here is the excerpt of the output:

    id  name           
------  ---------------
   103  Schmitt        
   112  King           
   114  Ferguson       
   119  Labrune        
   121  Bergulfsen     
   124  Nelson         
   125  Piestrzeniewicz
   128  Keitel         
   129  Murphy         
   131  Lee

When using ORDER BY to sort the result with UNION, you have to use it in the last SQL SELECT statement. It would be the best to parenthesize all the SELECT statements and place ORDER BY at the end.

Suppose you want to sort the combination of employees and customers in the query above by name and ID in ascending order.

(SELECT customerNumber id,contactLastname name
FROM customers)
UNION
(SELECT employeeNumber id,firstname name
FROM employees)
ORDER BY name,id

What will be displayed in the output if we don’t use alias for each column in the SELECT statements? MySQL will use the column names of the first SELECT statement as the label of the output.

(SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber, firstname
FROM employees)
ORDER BY contactLastname, customerNumber

MySQL also provides you another option to sort the result set based on column position in the ORDER BY clause as the following query:

(SELECT customerNumber, contactLastname
FROM customers)
UNION
(SELECT employeeNumber,firstname
FROM employees)
ORDER BY 2, 1
Related Tutorials
Search