《Mysql必讀深入理解mysql之left join 使用詳解》要點：
本文介紹了Mysql必讀深入理解mysql之left join 使用詳解，希望對您有用。如果有疑問，可以聯系我們。
?ON 子句與 WHERE 子句的不同
??? 一種更好地理解帶有 WHERE ... IS NULL 子句的復雜匹配條件的簡單辦法
??? Matching-Conditions 與 Where-conditions 的不同MYSQL必讀
關于 “A LEFT JOIN B ON 條件表達式” 的一點提醒MYSQL必讀
ON 條件(“A LEFT JOIN B ON 條件表達式”中的ON)用來決定如何從 B 表中檢索數據行.MYSQL必讀
如果 B 表中沒有任何一行數據匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數據MYSQL必讀
在匹配階段 WHERE 子句的條件都不會被使用.僅在匹配階段完成以后,WHERE 子句條件才會被使用.它將從匹配階段產生的數據中檢索過濾.MYSQL必讀
讓我們看一個 LFET JOIN 示例：MYSQL必讀
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)MYSQL必讀
ON 子句和 WHERE 子句有什么不同?MYSQL必讀
一個問題：下面兩個查詢的結果集有什么不同么?MYSQL必讀
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;MYSQL必讀
用例子來理解最好不過了：MYSQL必讀
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)MYSQL必讀
第一條查詢使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有數據行.MYSQL必讀
第二條查詢做了簡單的LEFT JOIN,然后使用 WHERE 子句從 LEFT JOIN的數據中過濾掉不符合條件的數據行.MYSQL必讀
再來看一些示例：MYSQL必讀
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)MYSQL必讀
所有來自product表的數據行都被檢索到了,但沒有在product_details表中匹配到記錄(product.id = product_details.id AND product.amount=100 條件并沒有匹配到任何數據)MYSQL必讀
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)MYSQL必讀
同樣,所有來自product表的數據行都被檢索到了,有一條數據匹配到了.MYSQL必讀
使用 WHERE ... IS NULL 子句的 LEFT JOINMYSQL必讀
當你使用 WHERE ... IS NULL 子句時會發生什么呢?MYSQL必讀
如前所述,WHERE 條件查詢發生在 匹配階段之后,這意味著 WHERE ... IS NULL 子句將從匹配階段后的數據中過濾掉不滿足匹配條件的數據行.MYSQL必讀
紙面上看起來很清楚,但是當你在 ON 子句中使用多個條件時就會感到困惑了.MYSQL必讀
我總結了一種簡單的方式來理解上述情況：MYSQL必讀
??? 將 IS NULL 作為否定匹配條件
??? 使用 !(A and B) == !A OR !B 邏輯判斷MYSQL必讀
看看下面的示例：MYSQL必讀
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)MYSQL必讀
讓我們檢查一下 ON 匹配子句：MYSQL必讀
(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)MYSQL必讀
我們可以把 IS NULL 子句 看作是否定匹配條件.MYSQL必讀
這意味著我們將檢索到以下行：MYSQL必讀
!( 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=1MYSQL必讀
就像在C語言中的邏輯 AND 和 邏輯 OR表達式一樣,其操作數是從左到右求值的.如果第一個參數做夠判斷操作結果,那么第二個參數便不會被計算求值(短路效果)MYSQL必讀
看看別的示例：MYSQL必讀
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)MYSQL必讀
Matching-Conditions 與 Where-conditions 之戰MYSQL必讀
如果你吧基本的查詢條件放在 ON 子句中,把剩下的否定條件放在 WHERE 子句中,那么你會獲得相同的結果.MYSQL必讀
例如,你可以不這樣寫：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;MYSQL必讀
你可以這樣寫：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;MYSQL必讀
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)MYSQL必讀
你可以不這樣寫：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;MYSQL必讀
可以這樣寫：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;MYSQL必讀
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)MYSQL必讀
這些查詢真的效果一樣?MYSQL必讀
如果你只必要第一個表中的數據的話,這些查詢會返回相同的結果集.有一種情況就是,如果你從 LEFT JOIN的表中檢索數據時,查詢的結果就不同了.MYSQL必讀
如前所屬,WHERE 子句是在匹配階段之后用來過濾的.MYSQL必讀
例如：MYSQL必讀
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)MYSQL必讀
總附注：MYSQL必讀
如果你使用 LEFT JOIN 來尋找在一些表中不存在的記錄,你必要做下面的測試：WHERE 部分的 col_name IS NULL(其中 col_name 列被定義為 NOT NULL),MYSQL 在查詢到一條匹配 LEFT JOIN 條件后將停止搜索更多行(在一個特定的組合鍵下). MYSQL必讀
《Mysql必讀深入理解mysql之left join 使用詳解》是否對您有啟發，歡迎查看更多與《Mysql必讀深入理解mysql之left join 使用詳解》相關教程，學精學透。維易PHP學院為您提供精彩教程。
