Mysql 简明教程
MySQL - UNION Operator
MySQL UNION Operator
MySQL 中的 UNION 运算符组合来自多个表的数据（无重复记录）。
The UNION operator in MySQL combines the data (without duplicate records) from multiple tables.
如果我们想按顺序从多个表或来自单个表的多组行中选择行，则可以使用 UNION 将它们全部作为一个单一的结果集。
We can use UNION if we want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set.
要在多张表上使用 UNION 运算符，所有这些表必须具有并集兼容性。并且仅当它们符合以下条件时，才认为它们具有并集兼容性 −
To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria −
-
The same number of columns selected with the same datatype.
-
These columns must also be in the same order.
-
They need not have same number of rows.
满足这些条件后，UNION 运算符会将来自多个表的行作为结果表返回，该表不包含任何这些表中的重复值。
Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables.
UNION 可用于 MySQL 4.0。本部分说明如何使用它。
UNION is available as of MySQL 4.0. This section illustrates how to use it.
Syntax
MySQL 中 UNION 运算符的基本语法如下 −
The basic syntax of UNION operator in MySQL is as follows −
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;Example
让我们首先使用以下 CREATE TABLE 查询创建名为 PROSPECT 的潜在客户表 −
Let us first create the prospective customers table named PROSPECT using the following CREATE TABLE query −
CREATE TABLE PROSPECT (
FNAME CHAR(20) NOT NULL,
LNAME CHAR(20),
ADDRESS VARCHAR(100) NOT NULL
);现在，我们使用以下 INSERT 语句向此表中插入记录 −
Now, we insert records into this table using INSERT statement below −
INSERT INTO PROSPECT VALUES
('Peter', 'Jones', '482 Rush St., Apt. 402'),
('Bernice', 'Smith', '916 Maple Dr.');PROSPECT 表创建为 −
The PROSPECT table is created as −
ACTIVE Table −
ACTIVE Table −
然后，我们使用以下 CREATE TABLE 查询创建名为 ACTIVE 的活跃客户表 −
We then create an active customers table named ACTIVE using the following CREATE TABLE query −
CREATE TABLE ACTIVE (
FNAME CHAR(20) NOT NULL,
LNAME CHAR(20),
ADDRESS VARCHAR(100) NOT NULL
);使用以下 INSERT 语句向 ACTIVE 表中插入记录 −
Using the following INSERT statement, insert records into the ACTIVE table −
INSERT INTO ACTIVE VALUES
('Grace', 'Peterson', '16055 Seminole Ave.'),
('Bernice', 'Smith', '916 Maple Dr.'),
('Walter', 'Brown', '8602 1st St.');ACTIVE 表的创建如下所示：
The ACTIVE table is created as −
现在，您想通过合并所有表中的名称和地址来创建一个单独的邮件列表。UNION 提供了一种实现此操作的方法。
Now, you want to create a single mailing list by merging names and addresses from all the tables. UNION provides a way to do this.
以下查询说明了如何同时从所有表中选择名称和地址：
The following query illustrates how to select names and addresses from the tables all at once −
SELECT FNAME, LNAME, ADDRESS FROM PROSPECT
UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE;UNION with WHERE clause
我们可以在 UNION 运算符中使用 WHERE 子句来在组合之前过滤每个 SELECT 语句的结果。
We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them.
Syntax
下面是使用 UNION 运算符与 WHERE 子句一起的语法 -
Following is the syntax for using the WHERE clause with UNION operator −
SELECT column1, column2, column3
FROM table1
WHERE column1 = 'value1'
UNION
SELECT column1, column2, column3
FROM table2
WHERE column1 = 'value2';Example
让我们使用上一个示例中的相同表，使用 UNION 运算符和 WHERE 子句来检索合并的记录：
Let us use the same tables from the previous example to retrieve combined records using UNION operator with WHERE clause −
SELECT FNAME, LNAME, ADDRESS FROM PROSPECT WHERE LNAME = 'Jones'
UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE WHERE LNAME = 'Peterson';UNION with ORDER BY clause
当我们在 ORDER BY 子句中使用 UNION 时，它会组合所有 SELECT 语句的有序结果集并生成单个的有序结果集。
When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set.
Syntax
以下是使用 UNION 运算符和 ORDER BY 子句的基本语法：
Following is the basic syntax to use UNION operator with ORDER BY clause −
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
ORDER BY column_name;UNION with Aliases
我们可以在 UNION 运算符的 MySQL 语句中使用别名，给表或列一个临时名称，这在使用具有相似的名称的多个表或列时非常有用。
We can use aliases in a MySQL statement of UNION operator to give a table or column a temporary name, which can be useful when working with multiple tables or columns with similar names.
与别名一起使用 UNION 时，需要注意的是列别名是由第一个 SELECT 语句决定的。因此，如果您想为不同 SELECT 语句中的同一列使用不同的别名，则需要在所有 SELECT 语句中使用列别名，以确保最终结果集中列名称的一致性。
When using UNION with aliases, it’s important to note that the column aliases are determined by the first SELECT statement. Therefore, if you want to use different aliases for the same column in different SELECT statements, you need to use column aliases in all SELECT statements to ensure consistent column names in the final result set.
Syntax
下面是用别名使用 Union 的语法 -
Following is the syntax for using Union with Aliases −
SELECT column1 AS alias1, column2 AS alias2
FROM table1
UNION
SELECT column3 AS alias1, column4 AS alias2
FROM table2;UNION ALL Operator
如果您想要选择所有记录（包括重复项），请使用第一个 UNION 关键字 ALL：
If you want to select all records, including duplicates, follow the first UNION keyword with ALL −
SELECT fname, lname, ADDRESS FROM prospect
UNION ALL
SELECT fname, lname, ADDRESS FROM active;UNION Operator Using Client Program
除了在 MySQL 服务器中直接在 MySQL 表中应用 UNION 运算符外，我们还可以使用客户端程序对 MySQL 表应用 UNION 操作。
In addition to applying the UNION Operator in MySQL table directly in MySQL server, we can also apply the UNION operation on a MySQL table using a client program.