SQL 語法 JOIN 與 UNION

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

多個資料表單有相對應的欄位時,我們可以透過 JOIN 來同時查詢多個資料表單的資料, 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

下面範例有兩個 Table.

  1. project (存放專案名稱與相對應人員名單)
    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.02 sec)
    

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

    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Ben');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Jason');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('A','Thomas');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Jack');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Andy');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('B','Chuck');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jack');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Jason');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO project (name , member) VALUES('C','Chuck');
    Query OK, 1 row affected (0.00 sec)
    
    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)
    
  2. staff (存放員工姓名與部門)
    staff 這個資料表主要儲存員工的相關資料.

    MariaDB [testdb]> CREATE TABLE staff (K1 int(11) NOT NULL auto_increment , name varchar(199) NOT NULL , dept varchar(199) NOT NULL, PRIMARY KEY (K1));
    Query OK, 0 rows affected (0.02 sec)
    
    MariaDB [testdb]> DESCRIBE staff;
    +-------+--------------+------+-----+---------+----------------+
    | Field | Type         | Null | Key | Default | Extra          |
    +-------+--------------+------+-----+---------+----------------+
    | K1    | int(11)      | NO   | PRI | NULL    | auto_increment |
    | name  | varchar(199) | NO   |     | NULL    |                |
    | dept  | varchar(199) | NO   |     | NULL    |                |
    +-------+--------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    

    並在 staff Table 建立了員工 Ben , Jason , Thomas , Jack , Andy , Chuck 與其相對應的部門資料.

    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Ben','HR');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Jason','HW');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Thomas','Test');
    Query OK, 1 row affected (0.01 sec)
    
    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Jack','PM');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Andy','Test');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> INSERT INTO staff (name , dept) VALUES('Chuck','SW');
    Query OK, 1 row affected (0.00 sec)
    
    MariaDB [testdb]> SELECT * FROM staff;
    +----+--------+------+
    | K1 | name   | dept |
    +----+--------+------+
    |  1 | Ben    | HR   |
    |  2 | Jason  | HW   |
    |  3 | Thomas | Test |
    |  4 | Jack   | PM   |
    |  5 | Andy   | Test |
    |  6 | Chuck  | SW   |
    +----+--------+------+
    6 rows in set (0.01 sec)
    

JOIN

在 project 資料表中有 member 姓名,在 staff 也有員工姓名,兩者的資料表皆有相對應的資料時就可以透過 Join 的方式直接把資料 SELECT 出來.Join 有下面幾種方式.

  • WHERE A=B
    利用 WHERE A=B 的指定方式查詢獲得 專案 A 與其成員和單位.

    SELECT project.name , project.member , staff.dept 
    FROM project , staff 
    WHERE project.member=staff.name AND project.name='A';
    
    MariaDB [testdb]> SELECT project.name , project.member , staff.dept FROM project , staff WHERE project.member=staff.name AND project.name='A';
    +------+--------+------+
    | name | member | dept |
    +------+--------+------+
    | A    | Ben    | HR   |
    | A    | Jason  | HW   |
    | A    | Thomas | Test |
    +------+--------+------+
    3 rows in set (0.00 sec)
    

    因為有多個表格,欄位指定會變很長,通常會使用表格別名來為個別資料表命名, project 別名為 p , staff 別名為 s , 使用 p 與 s 就可以直接代表 project 與 staff 資料表.

    SELECT p.name , p.member , s.dept 
    FROM project p, staff s 
    WHERE p.member=s.name AND p.name='A';
    
    MariaDB [testdb]> SELECT p.name , p.member , s.dept FROM project p, staff s WHERE p.member=s.name AND p.name='A';
    +------+--------+------+
    | name | member | dept |
    +------+--------+------+
    | A    | Ben    | HR   |
    | A    | Jason  | HW   |
    | A    | Thomas | Test |
    +------+--------+------+
    3 rows in set (0.00 sec)
    
  • INNER JOIN
    利用 Inner Join 的指定方式查詢獲得 專案 A 與其成員和單位.

    SELECT project.name , project.member , staff.dept 
    FROM project 
    INNER JOIN staff ON project.member = staff.name 
    WHERE project.name='A';
    
    MariaDB [testdb]> SELECT project.name , project.member , staff.dept FROM project INNER JOIN staff ON project.member = staff.name WHERE project.name='A';
    +------+--------+------+
    | name | member | dept |
    +------+--------+------+
    | A    | Ben    | HR   |
    | A    | Jason  | HW   |
    | A    | Thomas | Test |
    +------+--------+------+
    3 rows in set (0.01 sec)
    

    因為有多個表格,欄位指定會變很長,通常會使用表格別名來為個別資料表命名, project 別名為 p , staff 別名為 s , 使用 p 與 s 就可以直接代表 project 與 staff 資料表.

    SELECT p.name , p.member , s.dept 
    FROM project p 
    INNER JOIN staff s ON p.member = s.name 
    WHERE p.name='A';
    
    MariaDB [testdb]> SELECT p.name , p.member , s.dept FROM project p INNER JOIN staff s ON p.member = s.name WHERE p.name='A';
    +------+--------+------+
    | name | member | dept |
    +------+--------+------+
    | A    | Ben    | HR   |
    | A    | Jason  | HW   |
    | A    | Thomas | Test |
    +------+--------+------+
    3 rows in set (0.00 sec)
    
  • INNER JOIN USING
    前面範例使用 JOIN staff ON 如果後面兩個資料表欄位名稱皆相同時,可以用 JOIN staff USING(欄位名).
  • LEFT / RIGHT OUTER JOIN
    下面透過 GROUP_CONCAT 與 CONCAT 函數(可以把多個欄位文字整合在一起) 把所有的案子,成員與其相對應部門讀取出來.

    SELECT p.name , GROUP_CONCAT(CONCAT(p.member ,':',s.dept) SEPARATOR ',') AS "Members:Dept." 
    FROM project p 
    INNER JOIN staff s ON p.member = s.name 
    GROUP BY name;
    
    MariaDB [testdb]> SELECT p.name , GROUP_CONCAT(CONCAT(p.member ,':',s.dept) SEPARATOR ',') AS "Members:Dept." FROM project p INNER JOIN staff s ON p.member = s.name GROUP BY name;
    +------+-----------------------------+
    | name | Members:Dept.               |
    +------+-----------------------------+
    | A    | Jason:HW,Thomas:Test,Ben:HR |
    | B    | Andy:Test,Chuck:SW,Jack:PM  |
    | C    | Jack:PM,Jason:HW,Chuck:SW   |
    +------+-----------------------------+
    3 rows in set (0.00 sec)
    

    現在多了一個案子 D ,不過成員還沒決定.

    MariaDB [testdb]> INSERT INTO project (name) VALUES('D');
    Query OK, 1 row affected, 1 warning (0.00 sec)
    

    並透過剛剛的方式把所有的案子,成員與其相對應部門讀取出來.

    SELECT p.name , GROUP_CONCAT(CONCAT(p.member ,':',s.dept) SEPARATOR ',') AS "Members:Dept." 
    FROM project p 
    INNER JOIN staff s ON p.member = s.name 
    GROUP BY name;
    
    MariaDB [testdb]> SELECT p.name , GROUP_CONCAT(CONCAT(p.member ,':',s.dept) SEPARATOR ',') AS "Members:Dept." FROM project p INNER JOIN staff s ON p.member = s.name GROUP BY name;
    +------+-----------------------------+
    | name | Members:Dept.               |
    +------+-----------------------------+
    | A    | Jason:HW,Thomas:Test,Ben:HR |
    | B    | Andy:Test,Chuck:SW,Jack:PM  |
    | C    | Jack:PM,Jason:HW,Chuck:SW   |
    +------+-----------------------------+
    3 rows in set (0.00 sec)
    

    奇怪為什麼案子 D 的資料沒有出現, INNER JOIN 只會把符合 p.member = s.name 的資料顯示出來,案子 D 沒有 member 所以就不顯示,這時候需要改用 OUTER JOIN.

    OUTER JOIN 有 LEFT / RIGHT 兩種,這邊範例是以 LEFT (以 project 資料表為主 ) OUTER JOIN ,這時候就可以看到 案子 D 的資料了.

    SELECT p.name , GROUP_CONCAT(CONCAT(p.member ,':',s.dept) SEPARATOR ',') AS "Members:Dept." 
    FROM project p 
    LEFT OUTER JOIN staff s ON p.member = s.name 
    GROUP BY name;
    
    MariaDB [testdb]> SELECT p.name , GROUP_CONCAT(CONCAT(p.member ,':',s.dept) SEPARATOR ',') AS "Members:Dept." FROM project p LEFT OUTER JOIN staff s ON p.member = s.name GROUP BY name;
    +------+-----------------------------+
    | name | Members:Dept.               |
    +------+-----------------------------+
    | A    | Ben:HR,Thomas:Test,Jason:HW |
    | B    | Chuck:SW,Andy:Test,Jack:PM  |
    | C    | Jason:HW,Jack:PM,Chuck:SW   |
    | D    | NULL                        |
    +------+-----------------------------+
    4 rows in set (0.01 sec)
    

UNION

UNION 合併查詢,可以把一個以上的查詢結果合併為一個,前提是欄位名稱需相同.
下面兩個獨立的查詢,可以用 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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料