MySQL/MariaDB 資料庫 JOIN 查詢兩張資料表教學與範例

介紹如何使用 MySQL/MariaDB 資料庫的 JOIN 結合多張資料表產生所需的資料。

建立示範用資料

在說明 JOIN 的用法之前，我們先建立兩張示範用的資料表，第一張 persons 資料表用來儲存人員資料：

-- 建立 persons 資料表
CREATE TABLE persons (
  id INT NOT NULL AUTO_INCREMENT, -- 序號，整數、非空、自動遞增
  name VARCHAR(255),              -- 名字，可變長度的文字
  PRIMARY KEY (id)                -- 主鍵
);
-- 插入多筆資料至 persons 資料表
INSERT INTO persons VALUES (1, "Bond"), (2, "Cole"), (3, "Dana");
-- 查看 persons 資料表內所有資料
SELECT * FROM persons;

+----+------+
| id | name |
+----+------+
|  1 | Bond |
|  2 | Cole |
|  3 | Dana |
+----+------+

第二張 items 資料表用於儲存物品資料，而其中的 owner_id 欄位儲存的是擁有者的 ID，對應 persons 資料表的 id 欄位。

-- 建立 items 資料表
CREATE TABLE items (
  id INT NOT NULL AUTO_INCREMENT, -- 序號，整數、非空、自動遞增
  name VARCHAR(255),              -- 名字，可變長度的文字
  owner_id INT,                   -- 擁有者 ID 序號
  PRIMARY KEY (id)                -- 主鍵
);
-- 插入多筆資料至 items 資料表
INSERT INTO items (name, owner_id) VALUES
  ("A001", 2), ("A002", 2), ("A003", 3), ("A004", 4);
-- 查看 items 資料表內所有資料
SELECT * FROM items;

+----+------+----------+
| id | name | owner_id |
+----+------+----------+
|  1 | A001 |        2 |
|  2 | A002 |        2 |
|  3 | A003 |        3 |
|  4 | A004 |        4 |
+----+------+----------+

INNER JOIN

在查詢多張資料表的時候，最常見的就是使用 INNER JOIN 的方式查詢，將兩張資料表中有對應關係的資料顯示出來，例如查詢各擁有者所擁有的物品：

-- INNER JOIN 結合資料表
SELECT persons.name AS OwnerName, items.name AS ItemName
  FROM persons INNER JOIN items ON persons.id = items.owner_id;

+-----------+----------+
| OwnerName | ItemName |
+-----------+----------+
| Cole      | A001     |
| Cole      | A002     |
| Dana      | A003     |
+-----------+----------+

在使用 INNER JOIN 查詢的時候，只會顯示成功對應的結果（類似交集），如果某些人員沒有任何物品的話，就不會顯示出來，反之若有些物品的擁有者 ID 在 persons 中找不到時，也不會顯示出來。

INNER JOIN 也可以這樣寫，效果是相同的：

-- INNER JOIN 結合資料表
SELECT persons.name AS OwnerName, items.name AS ItemName
  FROM persons, items WHERE persons.id = items.owner_id;

LEFT JOIN

LEFT JOIN 會將左側資料表中的每一筆資料都顯示出來，如果在另外一張資料表中找不到對應關係的時候，則顯示 NULL。

如果我們想要盤點每一個人所擁有的物品，就算完全沒有任何物品也要顯示 NULL，就可以這樣寫：

-- LEFT JOIN 結合資料表
SELECT persons.name AS OwnerName, items.name AS ItemName
  FROM persons LEFT JOIN items ON persons.id = items.owner_id;

+-----------+----------+
| OwnerName | ItemName |
+-----------+----------+
| Cole      | A001     |
| Cole      | A002     |
| Dana      | A003     |
| Bond      | NULL     |
+-----------+----------+

RIGHT JOIN

RIGHT JOIN 剛好跟 LEFT JOIN 相反，它會將右側資料表中的每一筆資料都顯示出來，而如果在另外一張資料表中找不到對應關係的時候，則顯示 NULL。

例如盤點每一項物品的擁有者，如果找不到擁有者則顯示 NULL：

-- RIGHT JOIN 結合資料表
SELECT persons.name AS OwnerName, items.name AS ItemName
  FROM persons RIGHT JOIN items ON persons.id = items.owner_id;

+-----------+----------+
| OwnerName | ItemName |
+-----------+----------+
| Cole      | A001     |
| Cole      | A002     |
| Dana      | A003     |
| NULL      | A004     |
+-----------+----------+

CROSS JOIN

CROSS JOIN 是用來將兩張資料表中的所有資料進行所有可能的配對，列出所有可能的組合：

-- CROSS JOIN 結合資料表
SELECT persons.name AS OwnerName, items.name AS ItemName
  FROM persons CROSS JOIN items;

+-----------+----------+
| OwnerName | ItemName |
+-----------+----------+
| Bond      | A001     |
| Cole      | A001     |
| Dana      | A001     |
| Bond      | A002     |
| Cole      | A002     |
| Dana      | A002     |
| Bond      | A003     |
| Cole      | A003     |
| Dana      | A003     |
| Bond      | A004     |
| Cole      | A004     |
| Dana      | A004     |
+-----------+----------+

事實上當不加任何限制條件時，預設的 JOIN 動作就是 CROSS JOIN：

-- CROSS JOIN 結合資料表
SELECT persons.name AS OwnerName, items.name AS ItemName
  FROM persons JOIN items;