使用mysql的union来合并结果集

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 SELECT statement 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.

Related Tutorials

• Using MySQL SELECT Statement to Query Data

• Querying Data with MySQL IN Operator

• MySQL BETWEEN Operator Explained

• Using MySQL LIKE Operator to Select Data Based On Patterns

• How to Use MySQL DISTINCT to Eliminate Duplicate Rows

• Using MySQL LIMIT

• MySQL INNER JOIN

原文链接：http://outofmemory.cn/mysql/sql-union-mysql