mysql left join的深入探討

標籤：使用   key   val   sign   總結   pac   details   最好   prim   

即使你認為自己已對 MySQL 的 LEFT JOIN 理解深刻，但我敢打賭，這篇文章肯定大致也許可能讓你學會點東西！

• ON 子句與 WHERE 子句的不同
• 一種更好地理解帶有 WHERE ... IS NULL 子句的複雜匹配條件的簡單方法 
  
• Matching-Conditions 與 Where-conditions 的不同

關於 “A LEFT JOIN B ON 條件運算式” 的一點提醒

ON 條件（“A LEFT JOIN B ON 條件運算式”中的ON）用來決定如何從 B 表中檢索資料行。

如果 B 表中沒有任何一行資料匹配 ON 的條件,將會額外產生一行所有列為 NULL 的資料

在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以後，WHERE 子句條件才會被使用。它將從匹配階段產生的資料中檢索過濾。

讓我們看一個 LFET JOIN 樣本：

CREATE TABLE `product` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `amount` int(10) unsigned DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

CREATE TABLE `product_details` (  `id` int(10) unsigned NOT NULL,  `weight` int(10) unsigned DEFAULT NULL,  `exist` int(10) unsigned DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO product (id,amount)       VALUES (1,100),(2,200),(3,300),(4,400),(5,500);

INSERT INTO product_details (id,weight,exist)       VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1),(7,77,1)

 

 執行：

SELECT * FROM product LEFT JOIN product_detailsON (product.id = product_details.id);

ON 子句和 WHERE 子句有什麼不同？

一個問題：下面我們來看兩個查詢的結果集有什麼不同？

1. SELECT * FROM product LEFT JOIN product_details         ON (product.id = product_details.id)         AND   product_details.id=2;2. SELECT * FROM product LEFT JOIN product_details         ON (product.id = product_details.id)         WHERE product_details.id=2;

直接用例子來理解最好不過了：

第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有資料行。

第二條查詢做了簡單的LEFT JOIN，然後使用 WHERE 子句從 LEFT JOIN的資料中過濾掉不合格資料行。

再來看一些樣本：

所有來自product表的資料行都被檢索到了，但沒有在product_details表中匹配到記錄（product.id = product_details.id AND product.amount=100 條件並沒有匹配到任何資料），繼續看：

同樣，所有來自product表的資料行都被檢索到了，有一條資料匹配到了。

使用 WHERE ... IS NULL 子句的 LEFT JOIN

當你使用 WHERE ... IS NULL 子句時會發生什麼呢？

如前所述，WHERE 條件查詢發生在 匹配階段之後，這意味著 WHERE ... IS NULL 子句將從匹配階段後的資料中過濾掉不滿足匹配條件的資料行。

紙面上看起來很清楚，但是當你在 ON 子句中使用多個條件時就會感到困惑了。

我總結了一種簡單的方式來理解上述情況：

• 將 IS NULL 作為否定匹配條件
• 使用 !(A and B) == !A OR !B 邏輯判斷

看看下面的樣本：

SELECT a.*,b.id FROM product a LEFT JOIN product_details b       ON a.id=b.id AND b.weight!=44 AND b.exist=0       WHERE b.id IS NULL;

讓我們檢查一下 ON 匹配子句：

(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)

我們可以把 IS NULL 子句 看作是否定匹配條件。

看看別的樣本：

SELECT a.*,b.id,b.exist FROM product a LEFT JOIN product_details b       ON a.id=b.id AND b.weight!=44 AND b.exist=1       WHERE b.id IS NULL;

Matching-Conditions 與 Where-conditions 之戰

如果你把基本的查詢條件放在 ON 子句中，把剩下的否定條件放在 WHERE 子句中，那麼你會獲得相同的結果。

例如，你可以不這樣寫：

SELECT a.*,b.* FROM product a LEFT JOIN product_details bON a.id=b.id AND b.weight!=44 AND b.exist=0WHERE b.id IS NULL;

你可以這樣寫：

SELECT a.*,b.* FROM product a LEFT JOIN product_details bON a.id=b.idWHERE b.id is null OR b.weight=44 OR b.exist=1;

你可以不這樣寫：

SELECT a.*,b.* FROM product a LEFT JOIN product_details bON a.id=b.id AND b.weight!=44 AND b.exist!=0WHERE b.id IS NULL;

可以這樣寫：

SELECT a.*,b.* FROM product a LEFT JOIN product_details bON a.id=b.idWHERE b.id is null OR b.weight=44 OR b.exist=0;

這些查詢真的效果一樣？

如果你只需要第一個表中的資料的話，這些查詢會返回相同的結果集。有一種情況就是，如果你從 LEFT JOIN的表中檢索資料時，查詢的結果就不同了。

如前所屬，WHERE 子句是在匹配階段之後用來過濾的。

例如：

SELECT * FROM product a LEFT JOIN product_details b       ON a.id=b.id AND b.weight!=44 AND b.exist=1       WHERE b.id is null;

SELECT * FROM product a LEFT JOIN product_details b       ON a.id=b.id       WHERE b.id IS NULL OR b.weight=44 OR b.exist=0;

 

總結：

如果你使用 LEFT JOIN 來尋找在一些表中不存在的記錄，你需要做下面的測試：WHERE 部分的 col_name IS NULL(其中 col_name 列被定義為 NOT NULL)，MYSQL 在查詢到一條匹配 LEFT JOIN 條件後將停止搜尋更多行（在一個特定的按鍵組合下）。

mysql left join的深入探討