SQL 語法 – Multiple Tables JOIN

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

前面有討論到 SQL 語法 JOIN 與 UNION – https://benjr.tw/101855 ,可以把兩個 Table 的資料結合處理,多個 Table 也可以嗎?

是的,直接來看下面的範例.

[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)
   

3. dept (存放部門與部門敘述)
   dept 這個資料表主要儲存部門與其詳細相關資料.

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

   並在 dept Table 建立相對應的部門資料與其詳細資料.

   MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('HR','Human Resource');
   Query OK, 1 row affected (0.00 sec)
   
   MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('HW','HardWare');
   Query OK, 1 row affected (0.00 sec)
   
   MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('Test','Validation Test');
   Query OK, 1 row affected (0.00 sec)
   
   MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('PM','Project Manager');
   Query OK, 1 row affected (0.00 sec)
   
   MariaDB [testdb]> INSERT INTO dept (name , info) VALUES('SW','SoftWare');
   Query OK, 1 row affected (0.00 sec)
   

   MariaDB [testdb]> SELECT * FROM dept;
   +----+------+-----------------+
   | K1 | name | info            |
   +----+------+-----------------+
   |  1 | HR   | Human Resource  |
   |  2 | HW   | HardWare        |
   |  3 | Test | Validation Test |
   |  4 | PM   | Project Manager |
   |  5 | SW   | SoftWare        |
   +----+------+-----------------+
   5 rows in set (0.01 sec)
   

JOIN

在 project 資料表中有 member 姓名,在 staff 也有員工姓名,在 staff 也有部門資料, dept 資料表中也有部門資料.相對應資料如下.

Multiple Tables JOIN 語法如下.

SELECT p.name , p.member , s.dept , d.info
FROM project p 
INNER JOIN staff s ON p.member = s.name  
INNER JOIN dept d ON s.dept = d.name  
WHERE p.name='A';

MariaDB [testdb]> SELECT p.name , p.member , s.dept , d.info FROM project p INNER JOIN staff s ON p.member = s.name INNER JOIN dept d ON s.dept = d.name WHERE p.name='A';
+------+--------+------+-----------------+
| name | member | dept | info            |
+------+--------+------+-----------------+
| A    | Ben    | HR   | Human Resource  |
| A    | Jason  | HW   | HardWare        |
| A    | Thomas | Test | Validation Test |
+------+--------+------+-----------------+
3 rows in set (0.00 sec)