Sql 简明教程
SQL - UNION Operator
The SQL UNION Operator
SQL UNION 运算符通过消除重复行（如果有）来组合来自多张表的数据。
The SQL UNION operator is used to combine data from multiple tables by eliminating duplicate rows (if any).
要在多张表上使用 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.
Syntax
UNION 运算符的基本语法如下 -
The basic syntax of a UNION operator is as follows −
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition];此处，给定的条件可以是基于您的要求的任何给定表达式。
Here, the given condition could be any given expression based on your requirement.
UNION on a Single Field
如果我们想使用 UNION 将单个字段上两个或更多 SELECT 语句的结果集组合在一起，我们只需在每个查询的 SELECT 语句中包含该字段。UNION 运算符会自动删除最终结果集中任何重复的值。
If we want to use UNION to combine the result sets of two or more SELECT statements on a single field, we can simply include that field in the SELECT statement of each query. The UNION operator will automatically remove any duplicate values in the final result set.
Example
假设我们已使用 CREATE TABLE 语句在 MySQL 数据库中创建了一个名为 CUSTOMERS 的表，如下所示 −
Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);以下查询使用 INSERT 语句将值插入此表：
Following query inserts values into this table using the INSERT statement −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);CUSTOMERS 表如下 -
The CUSTOMERS table is as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
现在，使用 CREATE TABLE 语句创建第二个表 ORDERS，如下所示 -
Now, creating the second table ORDERS using CREATE TABLE statement as shown below −
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE DATETIME NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT INT NOT NULL,
PRIMARY KEY (OID)
);以下查询使用 INSERT 语句将值插入此表：
Following query inserts values into this table using the INSERT statement −
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000),
(100, '2009-10-08 00:00:00', 3, 1500),
(101, '2009-11-20 00:00:00', 2, 1560),
(103, '2008-05-20 00:00:00', 4, 2060);ORDERS 表如下 -
The ORDERS table is as follows −
OID |
DATE |
CUSTOMER_ID |
AMOUNT |
102 |
2009-10-08 00:00:00 |
3 |
3000.00 |
100 |
2009-10-08 00:00:00 |
3 |
1500.00 |
101 |
2009-11-20 00:00:00 |
2 |
1560.00 |
103 |
2008-05-20 00:00:00 |
4 |
2060.00 |
使用以下查询，让我们组合 CUSTOMERS 和 ORDERS 表中的 SALARY 和 AMOUNT 列（因为这些列具有相似的的数据类型） -
Using the following query, let us combine the SALARY and AMOUNT columns from CUSTOMERS and ORDERS table (since these columns have similar datatypes) −
SELECT SALARY FROM CUSTOMERS UNION SELECT AMOUNT FROM ORDERS;UNION on Multiple Fields
当我们在多个字段上使用 UNION 时，每个 SELECT 语句中的字段的数量和顺序都必须匹配。此外，每个 SELECT 语句中字段的数据类型必须兼容，以便 UNION 正确工作。如果数据类型不兼容，您可能需要使用转换函数（如 CAST 或 CONVERT）来确保数据类型匹配。
When we use UNION on multiple fields, the number and order of the fields in each SELECT statement must match. Also, the data types of the fields in each SELECT statement must be compatible for the UNION to work correctly. If the data types are not compatible, you may need to use conversion functions such as CAST or CONVERT to ensure that the data types match.
Example
由于 CUSTOMERS 和 ORDERS 表单独不是联合兼容的，因此让我们首先使用左联接和右联接将这两个表联接到一个更大的表中。检索到的联接表将具有相同数量的、相同数据类型的列，从而成为联合兼容的。现在，使用如下所示的 UNION 查询组合这些表 -
As the CUSTOMERS and ORDERS tables are not union-compatible individually, let us first join these two tables into a bigger table using Left Join and Right Join. The joined tables retrieved will have same number of columns with same datatypes, becoming union compatible. Now, these tables are combined using UNION query shown below −
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;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
在以下查询中，我们检索 id 分别大于 5 和 2 的客户的 id，分别来自“CUSTOMERS”和“ORDERS”表 -
In the following query, we are retrieving the id’s of the customers where id is greater than 5 and 2 from the 'CUSTOMERS' and 'ORDERS' tables respectively −
SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2;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.
Example
在这里，我们检索 id 分别大于 5 和 2 的客户，分别来自“CUSTOMERS”和“ORDERS”表，从其工资中从小到大进行排序 -
In here, we are retrieving the id’s of the customers where id is greater than 5 and 2 from the 'CUSTOMERS' and 'ORDERS' tables respectively, sorted low to high from their salary −
SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2
ORDER BY SALARY;UNION with Aliases
我们可以在 UNION 运算符的 SELECT 语句中使用别名，为表或列赋予一个临时名称，当使用多个表或具有相似名称的列时，这可能非常有用。
We can use aliases in the SELECT 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;Example
以下查询同时从两个表中检索所有 ID，并指示每个 ID 是属于客户还是客户下的订单−
The following query retrieves all the id’s from both tables, along with an indication of whether each id is of the customer or the order made by them −
SELECT ID, 'customer' AS type FROM CUSTOMERS
UNION
SELECT OID, 'order' AS type FROM ORDERS;Output
下面是生成的输出−
Following is the output produced −
ID |
type |
1 |
customer |
2 |
customer |
3 |
customer |
4 |
customer |
5 |
customer |
6 |
customer |
7 |
customer |
100 |
order |
101 |
order |
102 |
order |
103 |
order |
还有两个与 UNION 运算符类似的运算符。
There are two other operators which are like the UNION operator.
-
SQL INTERSECT Operator − This is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.
-
SQL EXCEPT Operator − This combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.