SQL 語法 – JOIN

adminLinux, MariaDB, Mysql, RedHat / CentOS

Loading

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

多個資料表單有相對應的欄位時,我們可以透過 JOIN 來同時查詢多個資料表單的資料.

[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') , ('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)
  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') , ('Jason','HW') , ('Thomas','Test') , ('Jack','PM') , ('Andy','Test') , ('Chuck','SW');
Query OK, 6 rows affected (0.002 sec)
Records: 6  Duplicates: 0  Warnings: 0

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)
  • CROSS JOIN
    就字面上的字義,把所有相對應資料全部交叉顯示,如下把 Project A 的資料全部對交叉顯示.

    SELECT project.name , project.member , staff.dept 
FROM project 
CROSS JOIN staff
WHERE project.name='A';

    執行結果.

    MariaDB [testdb]> SELECT project.name , project.member , staff.dept 
    -> FROM project 
    -> CROSS JOIN staff
    -> WHERE project.name='A';
+------+--------+------+
| name | member | dept |
+------+--------+------+
| A    | Ben    | HR   |
| A    | Ben    | HW   |
| A    | Ben    | Test |
| A    | Ben    | PM   |
| A    | Ben    | Test |
| A    | Ben    | SW   |
| A    | Jason  | HR   |
| A    | Jason  | HW   |
| A    | Jason  | Test |
| A    | Jason  | PM   |
| A    | Jason  | Test |
| A    | Jason  | SW   |
| A    | Thomas | HR   |
| A    | Thomas | HW   |
| A    | Thomas | Test |
| A    | Thomas | PM   |
| A    | Thomas | Test |
| A    | Thomas | SW   |
+------+--------+------+
18 rows in set (0.000 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)
  • SELECT INNER JOIN (Right / LEFT OUTER) (SELECT)
    JOIN 後面除了接資料表外還可以使用 SELECT 得來的資料.

    SELECT p.name , p.member , s.dept FROM project p 
INNER JOIN 
(SELECT * FROM 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 (SELECT * FROM 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)
  • SELECT (SELECT) INNER (Right / LEFT OUTER) JOIN (SELECT)
    如果是兩個查詢 (SELECT) 後的資料表 (Table) ,一樣可以使用 JOIN ,把兩個查詢後資料表 (Table) 結合再一起.

    要來看 A 案子的成員是否與 C 案子有重覆.

    SELECT a.member , a.name , b.name FROM
(SELECT name , member FROM project WHERE name='A') AS a

LEFT OUTER JOIN
(SELECT name , member FROM project WHERE name='C') AS b

ON a.member = b.member;

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

MariaDB [testdb]> SELECT name , member FROM project WHERE name='C';
+----+------+--------+
| K1 | name | member |
+----+------+--------+
|  7 | C    | Jack   |
|  8 | C    | Jason  |
|  9 | C    | Chuck  |
+----+------+--------+
3 rows in set (0.001 sec)

MariaDB [testdb]> SELECT a.member , a.name , b.name FROM (SELECT name , member FROM project WHERE name='A') AS a LEFT OUTER JOIN (SELECT name , member FROM project WHERE name='C') AS b ON a.member = b.member;
+--------+------+------+
| member | name | name |
+--------+------+------+
| Jason  | A    | C    |
| Ben    | A    | NULL |
| Thomas | A    | NULL |
+--------+------+------+
3 rows in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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