首页 > 代码库 > 14.UNION 和 UNION ALL 操作符
14.UNION 和 UNION ALL 操作符
union all union
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意,UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每条 SELECT 语句中的列的顺序必须相同。
CREATE TABLE Employees_China(
E_ID INT NOT NULL,
E_Name CHAR (25) NOT NULL
);
CREATE TABLE Employees_USA(
E_ID INT NOT NULL,
E_Name CHAR (25) NOT NULL
);
INSERT INTO Employees_China (E_ID,E_Name) VALUES(1,‘Zhang, Hua‘);
INSERT INTO Employees_China (E_ID,E_Name) VALUES(2, ‘Wang, Wei‘);
INSERT INTO Employees_China (E_ID,E_Name) VALUES(3,‘Carter, Thomas‘);
INSERT INTO Employees_China (E_ID,E_Name) VALUES(4,‘Yang, Ming‘);
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(1,‘Adams, John‘);
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(2,‘Bush, George‘);
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(3,‘Carter, Thomas‘);
INSERT INTO Employees_USA (E_ID,E_Name) VALUES(4,‘Gates, Bill‘);
mysql> select * from Employees_China;
+------+----------------+
| E_ID | E_Name |
+------+----------------+
| 1 | Zhang, Hua |
| 2 | Wang, Wei |
| 3 | Carter, Thomas |
| 4 | Yang, Ming |
+------+----------------+
4 rows in set (0.06 sec)
mysql> select * from Employees_USA;
+------+----------------+
| E_ID | E_Name |
+------+----------------+
| 1 | Adams, John |
| 2 | Bush, George |
| 3 | Carter, Thomas |
| 4 | Gates, Bill |
+------+----------------+
4 rows in set (0.00 sec)
1.
mysql> SELECT E_Name FROM Employees_China
-> UNION
-> SELECT E_Name FROM Employees_USA;
+----------------+
| E_Name |
+----------------+
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Gates, Bill |
+----------------+
7 rows in set (0.06 sec)
注释:默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
2.
mysql> SELECT E_Name FROM Employees_China
-> UNION ALL
-> SELECT E_Name FROM Employees_USA;
+----------------+
| E_Name |
+----------------+
| Zhang, Hua |
| Wang, Wei |
| Carter, Thomas |
| Yang, Ming |
| Adams, John |
| Bush, George |
| Carter, Thomas |
| Gates, Bill |
+----------------+
8 rows in set (0.00 sec)
另外,UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
14.UNION 和 UNION ALL 操作符