在 MariaDB 数据库中，UNION运算符用于组合两个或更多SELECT语句的结果集。它删除各种SELECT语句之间的重复行。
语法
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
注意：
UNION运算符中的每个SELECT语句在具有相似数据类型的结果集中必须具有相同数量的字段。
1. 使用 UNION 操作符返回单个字段
下面来看一个从多个SELECT语句返回一个字段的UNION运算符的例子。(两个表具有相同的公共字段)。
假设有两张表:students和teachers。对应的表结构和数据如下 -
students表中的数据：
MariaDB [testdb]> select * from students;
+------------+--------------+-----------------+----------------+
| student_id | student_name | student_address | admission_date |
+------------+--------------+-----------------+----------------+
| 1 | Maxsu | Haikou | 2017-01-07 |
| 2 | JMaster | Beijing | 2016-05-07 |
| 3 | Mahesh | Guangzhou | 2016-06-07 |
| 4 | Kobe | Shanghai | 2016-02-07 |
| 5 | Blaba | Shenzhen | 2016-08-07 |
+------------+--------------+-----------------+----------------+
5 rows in set (0.00 sec)
teachers表中的数据：
USE testdb;
CREATE TABLE teachers(
teacher_id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
address VARCHAR(40) NOT NULL,
admission_date DATE,
PRIMARY KEY ( teacher_id )
);
-- 插入数据
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(1,'陈小祥','上海','2013-06-07 00:00:00');
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(2,'张天经','广州','2013-08-08 00:00:00');
INSERT INTO teachers
(teacher_id, name, address, admission_date)
VALUES(3,'李四光','三亚','2014-09-07 00:00:00');
经过上创建和插入数据，现在teachers表中拥有以下数据记录 -
MariaDB [testdb]> select * from teachers;
+------------+--------+---------+----------------+
| teacher_id | name | address | admission_date |
+------------+--------+---------+----------------+
| 1 | 陈小祥 | 上海 | 2013-06-07 |
| 2 | 张天经 | 广州 | 2013-08-08 |
| 3 | 李四光 | 三亚 | 2014-09-07 |
+------------+--------+---------+----------------+
3 rows in set (0.08 sec)
假设现在要查询所有学生和老师的姓名，以及他们的地址，可参考以下查询语句 -
SELECT student_name AS name, student_address as address
FROM Students
UNION
SELECT name,address
FROM teachers;
执行上面查询语句，得到以下结果 -
MariaDB [testdb]> SELECT student_name AS name, student_address as address
-> FROM Students
-> UNION
-> SELECT name,address
-> FROM teachers;
+---------+-----------+
| name | address |
+---------+-----------+
| Maxsu | Haikou |
| JMaster | Beijing |
| Mahesh | Guangzhou |
| Kobe | Shanghai |
| Blaba | Shenzhen |
| 陈小祥 | 上海 |
| 张天经 | 广州 |
| 李四光 | 三亚 |
+---------+-----------+
8 rows in set (0.05 sec)
它显示了两个表中并删除重复的name列的值。
2. 具有 ORDER BY 子句的 UNION 运算符
使用ORDER BY子句的UNION运算符从两个表中检索多个列。参考以下语句 -
SELECT student_id, student_name
FROM Students
WHERE student_name = 'Maxsu'
UNION
SELECT teacher_id, address
FROM teachers
WHERE teacher_id > 1
ORDER BY 1;
执行上面示例代码，得到以下结果 -
MariaDB [testdb]> SELECT student_id, student_name
-> FROM Students
-> WHERE student_name = 'Maxsu'
-> UNION
-> SELECT teacher_id, address
-> FROM teachers
-> WHERE teacher_id > 1
-> ORDER BY 1;
+------------+--------------+
| student_id | student_name |
+------------+--------------+
| 1 | Maxsu |
| 2 | 广州 |
| 3 | 三亚 |
+------------+--------------+
3 rows in set (0.00 sec)
3. 一些其它的用法
具有不同列名的表之间的 UNION：
(SELECT e_name AS name, email FROM employees)
UNION
(SELECT c_name AS name, email FROM customers);
指定 UNION 的全局顺序并限制总行数：
(SELECT name, email FROM employees)
UNION
(SELECT name, email FROM customers)
ORDER BY name LIMIT 10;
添加一个常数行：
(SELECT 'John Doe' AS name, 'john.doe@example.net' AS email)
UNION
(SELECT name, email FROM customers);
不同的类型：
SELECT CAST('x' AS CHAR(1)) UNION SELECT REPEAT('y',4);
+----------------------+
| CAST('x' AS CHAR(1)) |
+----------------------+
| x |
| yyyy |
+----------------------+
按照每个 SELECT 使用排序列的顺序返回结果：
(SELECT 1 AS sort_column, e_name AS name, email FROM employees)
UNION
(SELECT 2, c_name AS name, email FROM customers) ORDER BY sort_column;