| ｜關聯式資料庫｜Index｜Key｜資料正規化｜SQL (1)｜SQL (2)｜SQL (3)｜SQL (4)｜ | ||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 資料庫簡介 |
| |||||||||||||||||||||||||||||
|
SQL：跨資料表的檢索 | ||||||||||||||||||||||||||||||
JOIN | ||||||||||||||||||||||||||||||
| 當資料分處於多個資料表時，我們得使用 JOIN 來進行跨資料表的檢索。 | ||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||
| •列出各人每筆收入： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee, salary WHERE employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
| 您也可以改用下列的語法： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee INNER JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
| 由於 employee 與 salary 之間是以同名的 employee_id 欄位來結合，所以也可以改用下列的語法： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee INNER JOIN salary USING( employee_id ) | ||||||||||||||||||||||||||||||
| 採用上述三種語法，均會忽略 Chuang 的資料（因為 employee 中有其資料，而 salary 中則無）；若要將此人的資料也一併列出的話，請改用下列的語法，其結果將會多出一筆（Chuang, NULL）的資料。 | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee LEFT JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
•計算各人收入總和： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, SUM( salary.amount ) FROM employee, salary WHERE employee.employee_id = salary.employee_id GROUP BY salary.employee_id | ||||||||||||||||||||||||||||||
| 嫌資料表名稱太長的話，可以改用「別名」來取代： | ||||||||||||||||||||||||||||||
|
SELECT E.realname, SUM( S.amount ) FROM employee AS E, salary AS S WHERE E.employee_id = S.employee_id GROUP BY S.employee_id | ||||||||||||||||||||||||||||||
|
•計算各部門收入總和： | ||||||||||||||||||||||||||||||
|
SELECT employee.department, SUM( salary.amount ) FROM employee, salary WHERE employee.employee_id = salary.employee_id GROUP BY employee.department | ||||||||||||||||||||||||||||||
|
Sub-select：MySQL 不支援的功能 | ||||||||||||||||||||||||||||||
| 有些資料庫產品支援「Sub-select」的功能，簡單地說，就是 SELECT 中有另一個 SELECT，但 MySQL 到 3.23.52 版為止都尚未提供，如： | ||||||||||||||||||||||||||||||
| •找出有收入者： | ||||||||||||||||||||||||||||||
|
SELECT realname FROM employee WHERE employee_id IN ( SELECT employee_id FROM salary ) | ||||||||||||||||||||||||||||||
| 不過，MySQL 有變通的方法： | ||||||||||||||||||||||||||||||
|
SELECT DISTINCT employee.realname FROM employee, salary WHERE employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
•找出無收入者： | ||||||||||||||||||||||||||||||
|
SELECT realname FROM employee WHERE employee_id NOT IN ( SELECT employee_id FROM salary ) | ||||||||||||||||||||||||||||||
| MySQL 還是有變通的方法： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname FROM employee LEFT JOIN salary USING( employee_id ) WHERE salary.amount IS NULL | ||||||||||||||||||||||||||||||
|
再看 JOIN | ||||||||||||||||||||||||||||||
| 看過上述的內容之後，您可能對於 MySQL 中的 JOIN 種類感到混淆，我將它們簡單地歸納如下： | ||||||||||||||||||||||||||||||
| CROSS JOIN | ||||||||||||||||||||||||||||||
| 兩個表格在結合時，不指定任何條件，如： | ||||||||||||||||||||||||||||||
| SELECT employee.realname, salary.amount FROM employee, salary | ||||||||||||||||||||||||||||||
| SELECT employee.realname, salary.amount FROM employee JOIN salary | ||||||||||||||||||||||||||||||
| SELECT employee.realname, salary.amount FROM employee CROSS JOIN salary | ||||||||||||||||||||||||||||||
| 在這個例子中，employee 原有 4 筆資料，而 salary 有 5 筆資料，在 JOIN 之後，結果將是兩者資料筆數的乘積：20。就等於是將兩個資料表中，所有可能的組合全部列出來一樣，其結果在實務上不見得有意義。這種結合可被視為兩個資料表的「笛卡兒乘積（Cartesian product）」。《執行結果》 | ||||||||||||||||||||||||||||||
|
INNER JOIN | ||||||||||||||||||||||||||||||
| 兩個表格在結合時，指定彼此之間的結合條件，如： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee, salary WHERE employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee INNER JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
| 如此一來，只有符合結合條件（同時存在於彼此之間）的資料，才會被 JOIN 在一起。這是最常用的 JOIN 型式。《執行結果》 | ||||||||||||||||||||||||||||||
|
OUTER JOIN | ||||||||||||||||||||||||||||||
| 兩個表格在進行 INNER JOIN 時，只有彼此相符合的資料列才會被考慮到，除此之外，就完全被忽略了。相較於 INNER JOIN 的排他性，OUTER JOIN 則是「寬容」多了。 | ||||||||||||||||||||||||||||||
| 在進行 LEFT OUTER JOIN 時，除了彼此相符合的資料列以外，左方的資料表中不相符的資料列也會被強迫輸出，如： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee LEFT OUTER JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee LEFT JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
| 像 Chuang 這種僅出現在 empolyee 裡，不存在於 saraly 中的資料列，在搭配 NULL 值之後，也能被列在結果之中了。善用這種特殊結果，我們可以順利找到只存在前一資料表，而不存在於後一資料表的資料列。《執行結果》 | ||||||||||||||||||||||||||||||
|
NATURAL JOIN | ||||||||||||||||||||||||||||||
| NATURAL 又代表什麼？加上這個關鍵字之後，兩個表格在進行 JOIN 時，不必言明彼此的結合關係，兩者之間同名的欄位會被自動結合在一起。 | ||||||||||||||||||||||||||||||
| 所以，以下兩段語法的執行結果相同： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee INNER JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee NATURAL JOIN salary | ||||||||||||||||||||||||||||||
| 以下兩段語法的執行結果也是相同的： | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee LEFT JOIN salary ON employee.employee_id = salary.employee_id | ||||||||||||||||||||||||||||||
|
SELECT employee.realname, salary.amount FROM employee NATURAL LEFT JOIN salary | ||||||||||||||||||||||||||||||
|
| ||||||||||||||||||||||||||||||
| ｜關聯式資料庫｜Index｜Key｜資料正規化｜SQL (1)｜SQL (2)｜SQL (3)｜SQL (4)｜ | ||||||||||||||||||||||||||||||