Learning Objectives
- 說明正規化之後為什麼要連接表格，寫出用 WHERE 與用 JOIN … ON 的兩種連接寫法
- 看出沒有連接條件時的笛卡兒積，算出它的列數
- 用 LEFT JOIN 找出「沒有對應資料」的列，例如沒有派工的員工
- 用表格別名寫自我連接，並把三、四張表格串連起來
- 使用純量子查詢、IN、EXISTS 與衍生表格，說明 ERROR 1052、1248 的原因
- 分辨 UNION 與 UNION ALL
資料分散在好幾張表格
第 03 課做正規化時，把「員工在哪個部門」拆成兩張表格：employee 只記部門編號 departmentID，
部門的名稱存在 department。這樣部門改名只要改一列，不會出現同一個部門在不同列寫成不同名稱的情形。
代價是查詢時要把表格連接（join）回來，才看得到「Nora Edwards 在 Research and Development 部門」這樣完整的資訊。
employee 資料庫裡的關聯如下（箭頭是外鍵指向的主鍵）：
| 表格 | 外鍵欄位 | 指向 |
|---|---|---|
employee |
departmentID |
department.departmentID |
employeeSkills |
employeeID |
employee.employeeID |
assignment |
clientID |
client.clientID |
assignment |
employeeID |
employee.employeeID |
連接條件通常就是「外鍵等於它指向的主鍵」。目前 assignment 只有一筆派工：員工 7513（Nora Edwards）替客戶 1（Telco Inc）工作。
連接兩張表格
最早的寫法是把兩張表格都寫在 FROM，用逗號隔開，再用 WHERE 寫連接條件：
SELECT employee.name, department.name
FROM employee, department
WHERE employee.departmentID = department.departmentID;
兩張表格都有 name 欄位，所以要寫成 表格.欄位。如果忘了寫連接條件，MySQL 會把兩張表格的每一列兩兩配對，
這叫做笛卡兒積（Cartesian product）：4 位員工 × 4 個部門＝16 列，其中大部分的配對沒有意義。
表格大的時候，笛卡兒積可能有幾百萬列，查詢會非常慢，這是多表查詢最常見的錯誤。
SQL-92 標準的寫法是 JOIN … ON，把連接條件和篩選條件分開，比較容易看出有沒有漏寫：
SELECT e.name, d.name AS dept
FROM employee AS e
JOIN department AS d ON e.departmentID = d.departmentID
WHERE e.job = 'Programmer';
JOIN和INNER JOIN相同：兩邊都有對應資料的列才會出現在結果裡- 兩張表格的連接欄位名稱相同時，可以寫
USING (departmentID)；結果裡departmentID只出現一次，不必再加表格名稱 - 表格別名（
e、d）讓欄位名稱寫起來比較短；定義別名之後就要用別名，寫employee.name會出現ERROR 1054 - MySQL 也接受沒有 ON 的
JOIN，再把條件寫在 WHERE（課程投影片的寫法之一），結果相同，但不建議這樣寫
從笛卡兒積到 JOIN … ON
第一個查詢沒有連接條件，JOIN 那一步會產生 16 列的笛卡兒積。第二個查詢多了 WHERE，16 列裡只有外鍵等於主鍵的 4 列留下來。第三、四個查詢改用 JOIN … ON 與 USING，結果相同。最後一個查詢會出現錯誤，想一想為什麼 departmentID 沒問題，name 卻有問題。
想一想：連接條件
employee 有 4 列、department 有 4 列。下面的查詢傳回幾列？
SELECT * FROM employee, department;
下面的查詢會得到什麼結果？
SELECT name FROM employee JOIN department USING (departmentID);
下面哪一個查詢和這個查詢的結果相同？
SELECT e.name, d.name
FROM employee e, department d
WHERE e.departmentID = d.departmentID;
LEFT JOIN 與 RIGHT JOIN
JOIN（內部連接）只留下兩邊都對得上的列。想知道「哪些員工還沒有派工」，內部連接做不到：
沒有派工的員工在 assignment 裡找不到對應的列，直接被排除了。
LEFT JOIN（左外部連接）保留左邊表格的每一列；右邊找不到對應資料時，右邊的欄位都填 NULL。
利用這一點，再篩選右邊是 NULL 的列，就能找出「沒有對應資料」的列：
SELECT e.name
FROM employee AS e
LEFT JOIN assignment AS a ON e.employeeID = a.employeeID
WHERE a.clientID IS NULL; -- 右邊沒有對應的派工
- 判斷 NULL 要寫
IS NULL，寫= NULL永遠得不到結果（第 08 課會說明原因） RIGHT JOIN反過來保留右邊表格的每一列。A RIGHT JOIN B和B LEFT JOIN A結果相同，多數人習慣統一寫 LEFT JOIN- MySQL 不支援
FULL OUTER JOIN，需要時用 LEFT JOIN 與 RIGHT JOIN 的結果做 UNION - 外部連接搭配 GROUP BY 計數時，要寫
COUNT(右邊的欄位)：右邊是 NULL 的列不會被算進去；寫COUNT(*)會把這一列也算成 1
找出沒有派工的員工
注意第一個查詢的 JOIN 那一步：只有 Nora Edwards 對得上派工，其他 3 位員工也留下來，clientID 與 workdate 是 NULL。第二個查詢多了 WHERE，只留下這 3 位。第三個查詢用 RIGHT JOIN 保留每個部門，Human Resources 與 Marketing 沒有員工，name 是 NULL。
自我連接與多張表格
自我連接（self join）是把同一張表格當成兩張來用。例如「找出和 Nora Edwards 同部門的員工」，
需要先在 employee 找到 Nora 的部門，再到 employee 找同部門的人。替同一張表格取兩個別名 e1、e2，
MySQL 就會把它們當成兩張表格：
SELECT e2.name
FROM employee e1, employee e2
WHERE e1.name = 'Nora Edwards' -- e1 只留下 Nora
AND e1.departmentID = e2.departmentID -- e2 是和 Nora 同部門的人
AND e2.name <> 'Nora Edwards'; -- 排除 Nora 自己
自我連接一定要用別名，否則 MySQL 分不出是哪一份表格。
多張表格的連接就是一連串的連接條件：連接 4 張表格需要 3 個連接條件，像接力一樣從一張表格走到下一張。
例如「客戶 Telco Inc 的工作由哪個部門負責」：client → assignment → employee → department。
寫成 JOIN 時，每加入一張表格就寫一個 JOIN … ON，連接條件和它要連的表格放在一起，比較不會漏寫。
自我連接與四張表格的串連
第一個查詢的 JOIN 那一步，同一張 employee 和自己配對成 16 列，WHERE 再留下符合的列。第二個查詢有 3 個 JOIN 步驟，每一步多加入一張表格，列數從 2、8 變成 32，最後才由 WHERE 篩到 1 列；第三個查詢是同一件事的 JOIN … ON 寫法，每一步都只留下對得上的列。（播放器依照 SQL 的邏輯順序呈現；真實的 MySQL 最佳化器會先套用條件、挑選連接順序，不會真的產生 32 列。）
子查詢
子查詢是寫在另一個查詢裡的查詢，用括號括起來。有些問題分成兩步想比較容易： 「工作時數最多的是誰」可以先算出最多的時數，再找出時數等於它的派工。
| 種類 | 範例 | 子查詢傳回 |
|---|---|---|
| 純量子查詢 | WHERE a.hours = (SELECT MAX(hours) FROM assignment) |
一個值 |
IN |
WHERE employeeID IN (SELECT employeeID FROM employeeSkills WHERE skill = 'Java') |
一欄、多列 |
EXISTS |
WHERE NOT EXISTS (SELECT * FROM employee e WHERE e.departmentID = d.departmentID) |
只看有沒有列 |
| 衍生表格 | FROM (SELECT … ) AS programmer |
一張暫時的表格 |
- 純量子查詢必須只傳回一列，傳回多列時會出現
ERROR 1242 (21000): Subquery returns more than 1 row EXISTS的子查詢用到外層的d.departmentID，叫做相關子查詢：外層每一列都要重新判斷一次- 寫在 FROM 的子查詢叫衍生表格，一定要取別名，否則出現
ERROR 1248 (42000): Every derived table must have its own alias - 很多子查詢也能改寫成 JOIN。MySQL 8 的最佳化器常會自動把 IN、EXISTS 子查詢轉成連接，可讀性優先，寫成自己容易理解的形式即可
UNION 把兩個查詢的結果上下合併。兩個 SELECT 的欄位數必須相同（否則 ERROR 1222），結果的欄位名稱取自第一個 SELECT。
UNION 會去掉重複的列，UNION ALL 保留全部的列；不需要去重複時用 UNION ALL，省下比對的工夫。
子查詢與 UNION
前三個查詢分別是純量子查詢、IN 與 NOT EXISTS。第四個查詢先在 FROM 算出只有 Programmer 的衍生表格，再和 assignment 連接；第五個查詢少了別名，出現 ERROR 1248。最後兩個查詢比較 UNION 與 UNION ALL：部門 128 有兩位 Programmer，UNION 只留下一列。
想一想：外部連接、子查詢與 UNION
department 有 4 個部門，其中 2 個部門沒有員工；有員工的兩個部門分別有 1 位、3 位員工。下面的查詢傳回幾列？
SELECT * FROM department d
LEFT JOIN employee e ON e.departmentID = d.departmentID;
下面的查詢會得到什麼結果？
SELECT name FROM (SELECT name FROM employee);
employee 的 departmentID 是 128、42、128、128，department 的 departmentID 是 42、128、129、130。下面的查詢傳回幾列？
SELECT departmentID FROM employee
UNION
SELECT departmentID FROM department;
動手寫：每個部門的員工人數
請列出每一個部門的名稱（欄位取名 dept）與員工人數（欄位取名 employees），沒有員工的部門也要出現，人數是 0。依人數由多到少排序，人數相同時依部門名稱排序。
重點整理
| 寫法 | 結果 |
|---|---|
FROM a, b（沒有條件） |
笛卡兒積：列數相乘 |
FROM a, b WHERE 條件、a JOIN b ON 條件 |
只留下兩邊對得上的列 |
a JOIN b USING (欄位) |
連接欄位同名時的簡寫，該欄位只出現一次 |
a LEFT JOIN b ON 條件 |
保留 a 的每一列，b 對不上時填 NULL |
… WHERE b.欄位 IS NULL |
找出 a 裡沒有對應資料的列 |
a RIGHT JOIN b |
保留 b 的每一列，等於 b LEFT JOIN a |
FROM t e1, t e2 |
自我連接，一定要用別名 |
UNION／UNION ALL |
上下合併結果；前者去重複，後者保留全部 |
- 連接 n 張表格需要 n − 1 個連接條件，漏寫會產生笛卡兒積
- 同名欄位要加表格名稱或別名，否則 ERROR 1052
- 衍生表格要取別名，否則 ERROR 1248；純量子查詢只能傳回一列，否則 ERROR 1242
下一課介紹 MySQL 的內建函式：處理 NULL、字串、數字與日期。