A2-02-21.DML-MySQL UNION
转载自：http://www.mysqltutorial.org/sql-union-mysql.aspx
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 of queries into a single result set. The following illustrates the syntax of the UNION operator:
|
1
2
3
4
5
6
|
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
UNION [DISTINCT | ALL]
SELECT column_list
...
|
To combine result set of two or more queries using the UNION operator, there are the basic rules that you must follow:
- First, the number and the orders of columns that appear in all
SELECTstatements must be the same. - Second, the data types of columns must be the same or convertible.
By default, the UNION operator removes duplicate rows even if you don’t specify the DISTINCT operator explicitly.
Let’s see the following sample tables: t1 and t2:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
CREATE TABLE t1 (
id INT PRIMARY KEY
);
CREATE TABLE t2 (
id INT PRIMARY KEY
);
INSERT INTO t1 VALUES (1),(2),(3);
INSERT INTO t2 VALUES (2),(3),(4);
|
The following statement combines result sets returned from t1 and t2 tables:
|
1
2
3
4
5
|
SELECT id
FROM t1
UNION
SELECT id
FROM t2;
|
The final result set contains the distinct values from separate result sets returned by the queries:
|
1
2
3
4
5
6
7
8
9
|
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.00 sec)
|
Because the rows with value 2 and 3 are duplicates, the UNION operator removed it and kept only distinct ones.
The following Venn diagram illustrates the union of two result sets that come from t1 and t2 tables:
If you use the UNION ALL explicitly, the duplicate rows, if available, remain in the result. Because UNION ALL does not need to handle duplicates, it performs faster than UNION DISTINCT .
|
1
2
3
4
5
|
SELECT id
FROM t1
UNION ALL
SELECT id
FROM t2;
|
|
1
2
3
4
5
6
7
8
9
10
11
|
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 2 |
| 3 |
| 4 |
+----+
6 rows in set (0.00 sec)
|
As you can see, the duplicates appear in the combined result set because of the UNION ALL operation.
UNION vs. JOIN
A JOIN combines result sets horizontally, a UNION appends result set vertically. The following picture illustrates the difference between UNION and JOIN:
MySQL UNION and column alias examples
We will use the customers and employees tables in the sample database for the demonstration:
Suppose you want to combine the first name and last name of both employees and customers into a single result set, you can use the UNION operator as follows:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT
firstName,
lastName
FROM
employees
UNION
SELECT
contactFirstName,
contactLastName
FROM
customers;
|
Here is the output:
As you can see, the MySQL UNION operator uses the column names of the first SELECT statement for labeling the columns in the output.
If you want to use your own column aliases, you need to specify them explicitly in the first SELECT statement as shown in the following example:
|
1
2
3
4
5
6
7
8
|
SELECT
concat(firstName,' ',lastName) fullname
FROM
employees
UNION SELECT
concat(contactFirstName,' ',contactLastName)
FROM
customers;
|
In this example, instead of using the default column label from the first query, we used a column alias fullname for labeling the output.
MySQL UNION and ORDER BY
If you want to sort the result of a union, you use an ORDER BY clause in the last SELECT statement as shown in the following example:
|
1
2
3
4
5
6
7
8
9
|
SELECT
concat(firstName,' ',lastName) fullname
FROM
employees
UNION SELECT
concat(contactFirstName,' ',contactLastName)
FROM
customers
ORDER BY fullname;
|
Notice that if you place the ORDER BY clause in each SELECT statement, it will not affect the order of the rows in the final result set.
MySQL also provides you with alternative option to sort a result set based on column position using ORDER BY clause as follows:
|
1
2
3
4
5
6
7
8
9
|
SELECT
concat(firstName,' ',lastName) fullname
FROM
employees
UNION SELECT
concat(contactFirstName,' ',contactLastName)
FROM
customers
ORDER BY 1;
|
In this tutorial, you have learned how to use MySQL UNION statement to combine data from multiple queries into a single result set.
