SQL UNION in MySQL

SQL UNION allows you to combine two or more result sets from select statements into a single result set. The usage of using SQL UNION is as follows:

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

…
The column list of each individual SELECT statement must have the same data type. By default the UNION removes all duplicated rows from the result set even if you don’t explicit using DISTINCT after the UNION keyword. If you use UNION ALL explicitly, the duplicated rows will remain in the result set. Let’s practice with couples of examples which use SQL UNION. Suppose you want to combine customers and employees into one, you just perform 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           

In order to use ORDER BY to sort the result you have to use it after the last SELECT statement. It would be the best to parenthesize all the SELECT statements and place ORDER BY after the last one. Suppose we use the want to sort the combination of employees and customers in the query above we can do as follows:

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

First it orders the result set by name and then by id What if we don’t use alias for each column in the SELECT statement? MySQL uses the column names in the first SELECT statement as the label of the result therefore you can rewrite the query above as follows:

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

or you can also use the column position in the ORDER BY clause like following query

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