MySQL 的 Join (結合) 語法

以往我在查詢兩個表的資料時候會有對應的 id 來做我的 where 條件查詢
這種寫法也是以前查到就是兩個關聯表格的 join 寫法
一直讓我沿用至今

今天寫資料庫遇到了一個問題
地圖資料表每個 tile 需要畫上顏色
而要畫上怎樣的顏色紀錄在國家資料表中

表：map

id	c_id
1	2
2	0
3	1

表：country

id	color
1	0
2	1

最笨的寫法就是程式中跑完所有地圖資料表的迴圈 每個迴圈再去找國家資料表的顏色
這樣的 SELECT 實在太多

要用以前知道 join 的語法又發現 不一定每個地圖資料表都有國家所屬
用我知道的就是

SELECT * FROM `map` , `country` WHERE `map`.`c_id` = `country`.`id`
這樣只會出現

id	c_id	id	color
1	2	2	1
3	1	1	0

我要的是 map 全部都出現 而有對應的 c_id 有就有 沒有就 NULL

直覺想到那我建立一個 country id = 0 的 NULL 資料 可是好蠢喔 雖然也是個方法

還是 google 一下還有沒有可以用的

查到這個 blog http://blog.yam.com/daidaibear/article/15009493

這裡可以知道我使用上的一些情況

我使用早期傳統的 where join 方式 但是 mysql join 語法會比 where 早解譯 使用 join 效能應該會比較快

而我要的答案就在 left join 以左邊(map)為主 不管有無 join 到 左邊資料表都會出來

舊式WHERE「結合」語法則以WHERE子句中的「*=」表示「左結合（left join）」

看到後馬上就去改語法 結果出現錯誤訊息 再往下看 ... 原來 mysql 不支援
好吧 那就好好寫新式 JOIN 語法了
SELECT * FROM `map` LEFT JOIN `country` ON `map`.`c_id` = `country`.`id`

id	c_id	id	color
1	2	2	1
2	0	NULL	NULL
3	1	1	0

 OK 是我要的 ...

而最後補充 mysql right join 也不支援 啊就 table 自己反過來寫成 left join 就好了 XD

題外話：
寫資料庫從來沒有看過資料庫的書 ... 會的就那幾招 不知道就 google 一下 ... 一知半解的這樣打江山 以前也常遇到效能很慘的時候 都是等使用者抱怨或是我自己也覺得很誇張才去最佳化程式碼跟 SQL 查詢條件 當同樣效果的查詢當效能提升的時候那種感動真的很不錯 只是也沒啥好感動 因為也是自己原來亂寫啊 XD




======================================================================
ref: http://blog.yam.com/daidaibear/article/15009493

11-1 JOIN 與 WHERE
「結合」在關聯式資料庫中所扮演的是整合的角色，當你在設計資料庫時，需要建立資料表之間的關聯，而資料庫建置完成在實際使用的時候，如果碰到的只是簡單 的資料處理，這交給一般基本的SQL指令都可以應付，但是往往需要處理的資料是跨越多個資料表的資料，這時「結合」就派上用場了。在下面的 SQL 敘述中使用 WHERE 敘述去關聯兩個資料表：

SELECT First_Name, Last_Name FORM Customers AS C, Orders AS O WHERE C.Customer_ID=O.Customer_ID;

上面這段蠻典型的使用「結合」的SQL敘述，其傳回的 First_Name 及 Last_Name 資料中，只有包含所有下了訂單的客戶資料，或是同時存在兩個資料中符合條件的資料；假如一個客戶有多筆定單，則你會看到多筆資料。上述SQL敘述改成 ANSI-92「結合」的標準寫法應寫成：

SELECT First_Name, Last_Name FROM Customers AS C JOIN Orders AS O on C.Customer_ID=O.Customer_ID;

早期要建立結合的關聯，都是用 WHERE 來達成的，因為這種方式比較直覺，且感覺上使用比較方便，因此大部分的資料庫設計者仍習慣使用 WHERE 的結合方式，各家的資料庫管理系統也都支援者兩種方式，在 MySQL 中 JOIN 的語法 會比 WHERE 語法先被解析，因此在大量使用時的執行效能應改會比 WHERE 要來的好。

基本ANSI-92的JOIN語法為：

SELECT column1,column2,.. FROM table1 JOIN table2 ON 結合準則 WHERE condition_criteria

這種JOIN語法是從基本SELECT語法擴充出來的。資料表1透過JOIN子句與資料表２的關係建立起來；最後的WHERE子句則是進一步定義此種 關聯關係，例如資料表間關聯的 key 值欄位。假設我們要將所有下了訂單的客戶資料列出來，則下面分別將新舊兩種「結合」語法列出比較。

舊式 WHERE 語法

SELECT C.First_Name, C.Last_Name, O.Order_Date, O.Order_ID FROM Customer AS C, Orders AS O WHERE C.Customer_ID=O.Customer_ID AND C.Customer_ID = 12

新式 JOIN 語法

SELECT C.First_Name, C.Last_Name, O.Order_Date, O.Order_ID FROM Customers AS C OIN Orders AS O ON C.Customer_ID = O.Customer_ID WHERE Customer_ID = 12

11-2 JOIN

使用新式語法或許可以享受到些ANSI-92標準所帶來的效能，但是為了可讀性，有些人往往會採用舊式語法。MySQL所支援的JOIN型態有:CROSS JOIN、INNER JOIN、LEFT JOIN及 NATURAL LEFT JOIN等四種，

CROSS JOIN (笛卡兒積)

這種 JOIN 不常使用，因為這種 JOIN，會將所有結合的資料表中的可能組合的紀錄全部列出來，對於這種情況有個專有名詞叫做笛卡兒結合(Cartesian join)，它們非常耗費CPU資源，舊式「結合」語法如下：

舊式 WHERE 語法

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customer AS C,Orders AS O;

新式 JOIN 語法

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customer AS C CROSS JOIN Orders AS O;

INNER JOIN (結合)
這是四種JOIN中最常使用的。經過JOIN的資料表，如果資料表中的資料不符合兩者「結合」關係的紀錄都不會被取出來，這是預設的「結合」型態。同樣的，我們也以新舊兩種「結合」語法舉例：

舊式 WHERE 語法

SELECT C.First_Name,C.Last_Name, O.Order_Id FROM Customer AS C, Order AS O WHERE C.Customer_ID = O.Customer_ID

新式 JOIN 語法

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customer AS C (INNER) JOIN Orders AS O ON C.Customer_ID = O.Customer_ID

INNER這個關鍵字可以省略不寫。
INNER JOIN是所有JOIN最常使用的

LEFT JOIN
會將結合關係中左右兩個資料表。左邊資料表在經過「結合」後，不管是否存在右邊資料表資料與之對應，仍然會將資料全部列出，相關範例如下：
舊式WHERE「結合」語法則以WHERE子句中的「*=」表示「左結合（left join）」。

舊式 WHERE 語法

SELECT C.First_Name, C.Last_Name, O.Order_ID FROM Customer AS C, Order AS O  WHERE C.Customer_ID *= O.Order_ID

這種語法經過MySQL的發展小組決定在MySQL不支援。他們所持的理由有兩個：一是此類 WHERE「左結合」鮮少使用，如果要用，可以使用標準ANSI-92的新式「LEFT JOIN」語法；另一個原因是是，MySQL發展經費及相關人員發展時間有限，此類少用的語法，似乎不需要浪費金錢與人力來做。

新式 JOIN 語法一

SELECT C.First_Name, C.Last_Name, O.Order_Id FORM Customer AS C　LEFT JOIN Order AS O ON C.Customer_ID = O.Customer_ID

這裡Customer資料表中所有紀錄都會列出，經過JOIN後，如果它的紀錄在Order中找不到對應紀錄的話，則會以NULL值代替。
ANSI-92 的 LEFT JOIN 相容語法還有另一種表達方式是利用 USING。上例可以改寫為：

新式 JOIN 語法二

SELECT C.First_Name, C.Last_Name, O.Orders_ID FORM Customers AS C LEFT JOIN Orders AS O USING Customer_ID

這裡使用 USING Customer_ID 取代 C.Customer_ID = O.Customer_ID。這種寫法也可參考看看。

NATURAL LEFT JOIN

其實就是 LEFT JOIN，差別是所有符合「結合」關係的欄位都自動選取，不需要在用 USING 或是 ON 之類的子句來特別註明，所以上述 LEFT JOIN 範例又可改寫為：

新式 JOIN 語法

SELECT C.First_Name, C.Last_Name, O.Orders_ID FORM Customers AS C NATURAL LEFT JOIN Orders AS O

由於Customers和Orders中都有Customer_ID，經由NATURAL LEFT JOIN關鍵字之後，便自動 LEFT JOIN 起來。這種「結合」用法不常見，所以要用的話，也跟 LEFT JOIN 一樣，只有支援標準的ANSI-92語法。

11-3 有 RIGHT JOIN 嗎?

幾乎每一種 RDMS 都有 RIGHT JOIN 這種語法，但是 MySQL 沒有支援 RIGHT JOIN 語法，不支援的原因很簡單，因為把 LEFT JOIN 的資料表對調再使用 LEFT JOIN 就可以得到 RIGHT JOIN 的效果，例如：

SELECT C.FIIRST_NAME FROM CUSTOMERS AS C LEFT JOIN ORDERS AS O ON C_CUSTOMER_ID=O.CUSTOMER_ID;

現在要取得 RIGHT JOIN 的效果只要兩個資料表對調：

SELECT C.FIIRST_NAME FROM ORDERS AS O LEFT JOIN CUSTOMERS AS C ON C_CUSTOMER_ID=O.CUSTOMER_ID;

因此實在沒有必要在 MySQL 中做一個 RIGHT JOIN，做一個 RIGHT JOIN 甚至還會降低資料庫的效能。