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:
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:
1 |
SELECT customerNumber id, contactLastname name |
4 |
SELECT employeeNumber id,firstname name |
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.
1 |
(SELECT customerNumber id,contactLastname name |
4 |
(SELECT employeeNumber id,firstname name |
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.
1 |
(SELECT customerNumber, contactLastname |
4 |
(SELECT employeeNumber, firstname |
6 |
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:
1 |
(SELECT customerNumber, contactLastname |
4 |
(SELECT employeeNumber,firstname |
Related posts:
- Using MySQL SELECT Statement to Query Data
- How to Use MySQL Distinct to Eliminate Duplicate Rows
- How to Use MySQL LIKE to Select Data Based on Patterns Matching
- Working with Database Table – Part I
- How to Use MySQL Limit to Constrain Number of Returned Records
