MariaDB Union運算子 - tw511教學網

在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, '[email protected]' 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;