Summary: in this tutorial, you will learn how to use MySQL UNION operator to combine two or more result sets from multiple SELECT statements into a single result set.
MySQL UNION Operator
MySQL UNION operator allows you to combine two or more result sets from multiple tables into a single result set. The syntax of the MySQL UNION is as follows:
SELECT column1, column2 UNION [DISTINCT | ALL] SELECT column1, column2 UNION [DISTINCT | ALL] …
There are some rules that you need to follow in order to use the UNION operator:
The number of columns appears in the corresponding SELECT statements must be equal.
The columns appear in the corresponding positions of each
SELECTstatement must have the same data type or at least convertible data type.
By default, the UNION operator eliminates duplicate rows from the result even if you don’t use DISTINCT operator explicitly. Therefore it is said that UNION clause is the shortcut of UNION DISTINCT.
If you use the UNION ALL explicitly, the duplicate rows, if available, remain in the result. The UNION ALL performs faster than the UNION DISTINCT.
MySQL UNION example
Let’s practice with an example of using MySQL UNION to get a better understanding.
Suppose you want to combine data from the customers and employees tables into a single result set, you can UNION operator as the following query:
SELECT customerNumber id, contactLastname name FROM customers UNION SELECT employeeNumber id,firstname name FROM employees
Here is the output:
MySQL UNION without Alias
In the example above, we used the column alias for each column in the SELECT statements. What would be the output if we didn’t use the column alias? MySQL uses the names of columns in the first SELECT statement as the labels for the output.
Let’s try the query that combines customers and employees information without using column alias:
(SELECT customerNumber, contactLastname FROM customers) UNION (SELECT employeeNumber, firstname FROM employees) ORDER BY contactLastname, customerNumber
The result has customerNumber and contactLastname as the label, which are the names of columns in the first SELECT statement.
MySQL UNION with ORDER BY
If you want to sort the results returned from the query using the UNION operator, you need to use ORDER BY clause in the last SQL SELECT statement. You can put each SELECT statement in the parentheses and use the ORDER BY clause as the last statement.
Let’s take a look at the following example:
(SELECT customerNumber id,contactLastname name FROM customers) UNION (SELECT employeeNumber id,firstname name FROM employees) ORDER BY name,id
In the query above, first we combine id and name of both employees and customers into one result set using the UNION operator. Then we sort the result set by using the ORDER BY clause. Notice that we put the SELECT statements inside the parentheses and place the ORDER BY clause as the last statement.
If you place the ORDER BY clause in each SELECT statement, it will not affect the order of the rows in the final result produced by the UNION operator.
MySQL also provides you with alternative option to sort the result set based on column position using ORDER BY clause as the following query:
(SELECT customerNumber, contactLastname FROM customers) UNION (SELECT employeeNumber,firstname FROM employees) ORDER BY 2, 1
In this tutorial, you have learned how to use MySQL UNION statement to combine data from multiple tables into a single result set.