15.4 What are the different types of joins? Please explain how they differ and why certain types are better in certain situations.
Join是用來聯合兩個表的，每個表至少需要有一列是相同的，不同的Join型別會返回不同的結果。我們來看一個例子，有兩個表，普通飲料和無卡飲料如下：
-- TABLE RegularBeverages
+-----------+-----------+ | Name | Code | +-----------+-----------+ | Budweiser | BUDWEISER | | Coca-Cola | COCACOLA | | Pepsi | PEPSI | +-----------+-----------+
-- TABLE CalorieFreeBeverages
+----------------+----------+ | Name | Code | +----------------+----------+ | Diet Coca-Cola | COCACOLA | | Fresca | FRESCA | | Diet Pepsi | PEPSI | | Pepsi Light | PEPSI | | Purfied water | Water | +----------------+----------+
如果我們想聯合這兩個表，有很多種Join可以使用：
內交Inner Join：只會顯示相同列匹配的項：
SELECT * FROM RegularBeverages INNER JOIN CalorieFreeBeverages ON RegularBeverages.Code = CalorieFreeBeverages.Code; +-----------+----------+----------------+----------+ | Name | Code | Name | Code | +-----------+----------+----------------+----------+ | Coca-Cola | COCACOLA | Diet Coca-Cola | COCACOLA | | Pepsi | PEPSI | Diet Pepsi | PEPSI | | Pepsi | PEPSI | Pepsi Light | PEPSI | +-----------+----------+----------------+----------+
外交Outer Join：外交會包含內交的結果，同時也會包含一些沒有匹配到的結果，外交有如下幾種：
- 左交Left Outer Join (Left Join)，會包含左表的所有結果，如果沒有匹配，右邊就是NULL：
SELECT * FROM RegularBeverages LEFT JOIN CalorieFreeBeverages ON RegularBeverages.Code = CalorieFreeBeverages.Code; +-----------+-----------+----------------+----------+ | Name | Code | Name | Code | +-----------+-----------+----------------+----------+ | Coca-Cola | COCACOLA | Diet Coca-Cola | COCACOLA | | Pepsi | PEPSI | Diet Pepsi | PEPSI | | Pepsi | PEPSI | Pepsi Light | PEPSI | | Budweiser | BUDWEISER | NULL | NULL | +-----------+-----------+----------------+----------+
- 右交Right Outer Join (Right Join)，返回右表的所有項，如果沒有匹配，左邊就是NULL：
SELECT * FROM RegularBeverages RIGHT JOIN CalorieFreeBeverages ON RegularBeverages.Code = CalorieFreeBeverages.Code; +-----------+----------+----------------+----------+ | Name | Code | Name | Code | +-----------+----------+----------------+----------+ | Coca-Cola | COCACOLA | Diet Coca-Cola | COCACOLA | | Pepsi | PEPSI | Diet Pepsi | PEPSI | | Pepsi | PEPSI | Pepsi Light | PEPSI | | NULL | NULL | Fresca | FRESCA | | NULL | NULL | Purfied water | Water | +-----------+----------+----------------+----------+
- 全交Full Outer Join，聯合左交和右交的結果，不論有沒有匹配都把結果顯示出來，由於MySQL中沒有這個命令，所以我們用Union把左右交的結果並起來：
SELECT * FROM RegularBeverages LEFT JOIN CalorieFreeBeverages ON RegularBeverages.Code = CalorieFreeBeverages.Code UNION SELECT * FROM RegularBeverages RIGHT JOIN CalorieFreeBeverages ON RegularBeverages.Code = CalorieFreeBeverages.Code; +-----------+-----------+----------------+----------+ | Name | Code | Name | Code | +-----------+-----------+----------------+----------+ | Coca-Cola | COCACOLA | Diet Coca-Cola | COCACOLA | | Pepsi | PEPSI | Diet Pepsi | PEPSI | | Pepsi | PEPSI | Pepsi Light | PEPSI | | Budweiser | BUDWEISER | NULL | NULL | | NULL | NULL | Fresca | FRESCA | | NULL | NULL | Purfied water | Water | +-----------+-----------+----------------+----------+
關於此內容還可以參見我之前的一篇部落格SQL Left Join, Right Join, Inner Join, and Natural Join 各種Join小結。