標籤：tar 需要 sig 使用 engine 樣本 mount default sql
https://www.oschina.net/question/89964_65912 即使你認為自己已對 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 樣本：
| 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556 |
mysql> 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=latin1 mysql> 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=latin1 mysql> INSERT INTO product (id,amount) VALUES (1,100),(2,200),(3,300),(4,400);Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0 mysql> INSERT INTO product_details (id,weight,exist) VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);Query OK, 4 rows affected (0.00 sec)Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM product;+----+--------+| id | amount |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 || 4 | 400 |+----+--------+4 rows in set (0.00 sec) mysql> SELECT * FROM product_details;+----+--------+-------+| id | weight | exist |+----+--------+-------+| 2 | 22 | 0 || 4 | 44 | 1 || 5 | 55 | 0 || 6 | 66 | 1 |+----+--------+-------+4 rows in set (0.00 sec) mysql> SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id);+----+--------+------+--------+-------+| id | amount | id | weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL || 2 | 200 | 2 | 22 | 0 || 3 | 300 | NULL | NULL | NULL || 4 | 400 | 4 | 44 | 1 |+----+--------+------+--------+-------+4 rows in set (0.00 sec) |
ON 子句和 WHERE 子句有什麼不同？一個問題：下面兩個查詢的結果集有什麼不同嗎？?
| 123456 |
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; |
用例子來理解最好不過了：?
| 12345678910111213141516171819202122 |
mysql> SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product_details.id=2;+----+--------+------+--------+-------+| id | amount | id | weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL || 2 | 200 | 2 | 22 | 0 || 3 | 300 | NULL | NULL | NULL || 4 | 400 | NULL | NULL | NULL |+----+--------+------+--------+-------+4 rows in set (0.00 sec) mysql> SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) WHERE product_details.id=2;+----+--------+----+--------+-------+| id | amount | id | weight | exist |+----+--------+----+--------+-------+| 2 | 200 | 2 | 22 | 0 |+----+--------+----+--------+-------+1 row in set (0.01 sec) |
第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有資料行。第二條查詢做了簡單的LEFT JOIN，然後使用 WHERE 子句從 LEFT JOIN的資料中過濾掉不合格資料行。再來看一些樣本：?
| 12345678910111213 |
mysql>mysql> SELECT * FROM product LEFT JOIN product_details ON product.id = product_details.id AND product.amount=100;+----+--------+------+--------+-------+| id | amount | id | weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL || 2 | 200 | NULL | NULL | NULL || 3 | 300 | NULL | NULL | NULL || 4 | 400 | NULL | NULL | NULL |+----+--------+------+--------+-------+4 rows in set (0.00 sec) |
所有來自product表的資料行都被檢索到了，但沒有在product_details表中匹配到記錄（product.id = product_details.id AND product.amount=100 條件並沒有匹配到任何資料）?
| 123456789101112 |
mysql> SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product.amount=200;+----+--------+------+--------+-------+| id | amount | id | weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL || 2 | 200 | 2 | 22 | 0 || 3 | 300 | NULL | NULL | NULL || 4 | 400 | NULL | NULL | NULL |+----+--------+------+--------+-------+4 rows in set (0.01 sec) |
同樣，所有來自product表的資料行都被檢索到了，有一條資料匹配到了。使用 WHERE ... IS NULL 子句的 LEFT JOIN當你使用 WHERE ... IS NULL 子句時會發生什麼呢？如前所述，WHERE 條件查詢發生在 匹配階段之後，這意味著 WHERE ... IS NULL 子句將從匹配階段後的資料中過濾掉不滿足匹配條件的資料行。紙面上看起來很清楚，但是當你在 ON 子句中使用多個條件時就會感到困惑了。我總結了一種簡單的方式來理解上述情況：
- 將 IS NULL 作為否定匹配條件
- 使用 !(A and B) == !A OR !B 邏輯判斷
看看下面的樣本：?
| 1234567891011 |
mysql> SELECT a.* 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;+----+--------+| id | amount |+----+--------+| 1 | 100 || 3 | 300 || 4 | 400 |+----+--------+3 rows in set (0.00 sec) |
讓我們檢查一下 ON 匹配子句：?
| 1 |
(a.id=b.id) AND (b.weight!=44) AND (b.exist=0) |
我們可以把 IS NULL 子句 看作是否定匹配條件。這意味著我們將檢索到以下行：?
| 123 |
!( exist(b.id that equals to a.id) AND b.weight !=44 AND b.exist=0 )!exist(b.id that equals to a.id) || !(b.weight !=44) || !(b.exist=0)!exist(b.id that equals to a.id) || b.weight =44 || b.exist=1 |
就像在C語言中的邏輯 AND 和 邏輯 OR運算式一樣，其運算元是從左至右求值的。如果第一個參數做夠判斷操作結果，那麼第二個參數便不會被計算求值（短路效果）看看別的樣本：?
| 123456789101112 |
mysql> SELECT a.* 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;+----+--------+| id | amount |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 || 4 | 400 |+----+--------+4 rows in set (0.00 sec) |
Matching-Conditions 與 Where-conditions 之戰如果你吧基本的查詢條件放在 ON 子句中，把剩下的否定條件放在 WHERE 子句中，那麼你會獲得相同的結果。例如，你可以不這樣寫：?
| 123 |
SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.id AND b.weight!=44 AND b.exist=0WHERE b.id IS NULL; |
你可以這樣寫：?
| 123 |
SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.idWHERE b.id is null OR b.weight=44 OR b.exist=1; |
?
| 1234567891011 |
mysql> SELECT a.* 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=1;+----+--------+| id | amount |+----+--------+| 1 | 100 || 3 | 300 || 4 | 400 |+----+--------+3 rows in set (0.00 sec) |
你可以不這樣寫：?
| 123 |
SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.id AND b.weight!=44 AND b.exist!=0WHERE b.id IS NULL; |
可以這樣寫：?
| 123 |
SELECT a.* FROM product a LEFT JOIN product_details bON a.id=b.idWHERE b.id is null OR b.weight=44 OR b.exist=0; |
?
| 123456789101112 |
mysql> SELECT a.* 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;+----+--------+| id | amount |+----+--------+| 1 | 100 || 2 | 200 || 3 | 300 || 4 | 400 |+----+--------+4 rows in set (0.00 sec) |
這些查詢真的效果一樣？如果你只需要第一個表中的資料的話，這些查詢會返回相同的結果集。有一種情況就是，如果你從 LEFT JOIN的表中檢索資料時，查詢的結果就不同了。如前所屬，WHERE 子句是在匹配階段之後用來過濾的。例如：?
| 12345678910111213141516171819202122232425 |
mysql> 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;+----+--------+------+--------+-------+| id | amount | id | weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL || 2 | 200 | NULL | NULL | NULL || 3 | 300 | NULL | NULL | NULL || 4 | 400 | NULL | NULL | NULL |+----+--------+------+--------+-------+4 rows in set (0.00 sec) mysql> 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;+----+--------+------+--------+-------+| id | amount | id | weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL || 2 | 200 | 2 | 22 | 0 || 3 | 300 | NULL | NULL | NULL || 4 | 400 | 4 | 44 | 1 |+----+--------+------+--------+-------+4 rows in set (0.00 sec) |
1總附註：如果你使用 LEFT JOIN 來尋找在一些表中不存在的記錄，你需要做下面的測試：WHERE 部分的 col_name IS NULL(其中 col_name 列被定義為 NOT NULL)，MYSQL 在查詢到一條匹配 LEFT JOIN 條件後將停止搜尋更多行（在一個特定的按鍵組合下）。
MySQL LEFT JOIN 你可能需要瞭解的三點
