SQL 語法 – UNION

測試環境為 CentOS 7 x86_64 虛擬機.

UNION 可以把一個以上的查詢結果合併為一個.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 25
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

建立 project (存放專案名稱與相對應人員名單)

MariaDB [testdb]> CREATE TABLE project (K1 INT(11) NOT NULL AUTO_INCREMENT  , name VARCHAR(199) NOT NULL , member VARCHAR(199) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.02 sec)

MariaDB [testdb]> DESCRIBE project;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| K1     | int(11)      | NO   | PRI | NULL    | auto_increment |
| name   | varchar(199) | NO   |     | NULL    |                |
| member | varchar(199) | NO   |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+
3 rows in set (0.005 sec)

在 project 資料表建立專案與個別成員資料資料.

MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Ben') , ('A','Jason') , ('A','Thomas') , ('B','Jack') , ('B','Andy') , ('B','Chuck') , ('C','Jack') , ('C','Jason') , ('C','Chuck');
Query OK, 9 rows affected (0.003 sec)
Records: 9  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM project;
+----+------+--------+
| K1 | name | member |
+----+------+--------+
|  1 | A    | Ben    |
|  2 | A    | Jason  |
|  3 | A    | Thomas |
|  4 | B    | Jack   |
|  5 | B    | Andy   |
|  6 | B    | Chuck  |
|  7 | C    | Jack   |
|  8 | C    | Jason  |
|  9 | C    | Chuck  |
+----+------+--------+
9 rows in set (0.00 sec)

UNION

UNION 合併查詢(可以把一個以上的查詢結果合併為一個,前提是欄位名稱需相同),預設為 DISTINCT ( 只會顯示不重覆的資料 ),可以使用 ALL 來顯示所有的資料.
下面兩個獨立的查詢,可以用 OR 或是 UNION 合併查詢的方式.

SELECT name,member 
FROM project 
WHERE name LIKE 'A';

MariaDB [testdb]> SELECT name,member FROM project WHERE name LIKE 'A';
+------+--------+
| name | member |
+------+--------+
| A    | Ben    |
| A    | Jason  |
| A    | Thomas |
+------+--------+
3 rows in set (0.00 sec)

SELECT name,member 
FROM project 
WHERE name LIKE 'B' ;

MariaDB [testdb]> SELECT name,member FROM project WHERE name LIKE 'B' ;
+------+--------+
| name | member |
+------+--------+
| B    | Jack   |
| B    | Andy   |
| B    | Chuck  |
+------+--------+
3 rows in set (0.00 sec)

使用 OR 的語法合併兩次 SELECT 出來的結果.

SELECT name,member 
FROM project 
WHERE name LIKE 'A' OR name LIKE 'B';

MariaDB [testdb]> SELECT name,member FROM project WHERE name LIKE 'A' OR name LIKE 'B';
+------+--------+
| name | member |
+------+--------+
| A    | Ben    |
| A    | Jason  |
| A    | Thomas |
| B    | Jack   |
| B    | Andy   |
| B    | Chuck  |
+------+--------+
6 rows in set (0.00 sec)

使用 UNION 的語法合併兩次 SELECT 出來的結果.

SELECT name,member 
FROM project 
WHERE name LIKE 'A' 
UNION 
SELECT name,member 
FROM project 
WHERE name LIKE 'B';

MariaDB [testdb]> SELECT name,member FROM project WHERE name LIKE 'A' UNION SELECT name,member FROM project WHERE name LIKE 'B';
+------+--------+
| name | member |
+------+--------+
| A    | Ben    |
| A    | Jason  |
| A    | Thomas |
| B    | Jack   |
| B    | Andy   |
| B    | Chuck  |
+------+--------+
6 rows in set (0.02 sec)