union mysql 同比_使用mysql的union来合并结果集

Summary:in this tutorial, you will learn how to use MySQL UNIONoperator to combine two or more result sets from multiple SELECTstatements into a single result set.

MySQL UNION Operator

MySQL UNIONoperator 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 UNIONoperator eliminates duplicate rows from the result even if you don’t use DISTINCToperator explicitly. Therefore it is said that UNIONclause 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 UNIONto get a better understanding.

Suppose you want to combine data from the  customers and employeestables into a single result set, you can UNIONoperator 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 SELECTstatements. What would be the output if we didn’t use the column alias? MySQL uses the names of columns in the first SELECTstatement 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 customerNumberand contactLastnameas the label,which are the names of columns in the first SELECTstatement.

MySQL UNION with ORDER BY

If you want to sort the results returned from the query using the UNIONoperator, you need to use ORDER BY clause in the last SQL SELECTstatement. You can put each SELECTstatement 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 idand nameof both employees and customers into one result set using the UNIONoperator. Then we sort the result set by using the ORDER BY clause. Notice that we put the SELECTstatements inside the parentheses and place the ORDER BY clause as the last statement.

If you place the ORDER BY clause in each SELECTstatement, it will not affect the order of the rows in the final result produced by the UNIONoperator.

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