深入理解mysql之left join 使用詳解
ON 子句與 WHERE 子句的不同
一種更好地理解帶有 WHERE ... IS NULL 子句的復(fù)雜匹配條件的簡(jiǎn)單方法
Matching-Conditions 與 Where-conditions 的不同
關(guān)于 “A LEFT JOIN B ON 條件表達(dá)式” 的一點(diǎn)提醒
ON 條件（“A LEFT JOIN B ON 條件表達(dá)式”中的ON）用來(lái)決定如何從 B 表中檢索數(shù)據(jù)行。
如果 B 表中沒(méi)有任何一行數(shù)據(jù)匹配 ON 的條件,將會(huì)額外生成一行所有列為 NULL 的數(shù)據(jù)
在匹配階段 WHERE 子句的條件都不會(huì)被使用。僅在匹配階段完成以后，WHERE 子句條件才會(huì)被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過(guò)濾。
讓我們看一個(gè) LFET JOIN 示例：
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 子句有什么不同？
一個(gè)問(wèn)題：下面兩個(gè)查詢(xún)的結(jié)果集有什么不同么？
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;
用例子來(lái)理解最好不過(guò)了：
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)
第一條查詢(xún)使用 ON 條件決定了從 LEFT JOIN的 product_details表中檢索符合的所有數(shù)據(jù)行。
第二條查詢(xún)做了簡(jiǎn)單的LEFT JOIN，然后使用 WHERE 子句從 LEFT JOIN的數(shù)據(jù)中過(guò)濾掉不符合條件的數(shù)據(jù)行。
再來(lái)看一些示例：
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)
所有來(lái)自product表的數(shù)據(jù)行都被檢索到了，但沒(méi)有在product_details表中匹配到記錄（product.id = product_details.id AND product.amount=100 條件并沒(méi)有匹配到任何數(shù)據(jù)）
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)
同樣，所有來(lái)自product表的數(shù)據(jù)行都被檢索到了，有一條數(shù)據(jù)匹配到了。
使用 WHERE ... IS NULL 子句的 LEFT JOIN
當(dāng)你使用 WHERE ... IS NULL 子句時(shí)會(huì)發(fā)生什么呢？
如前所述，WHERE 條件查詢(xún)發(fā)生在 匹配階段之后，這意味著 WHERE ... IS NULL 子句將從匹配階段后的數(shù)據(jù)中過(guò)濾掉不滿(mǎn)足匹配條件的數(shù)據(jù)行。
紙面上看起來(lái)很清楚，但是當(dāng)你在 ON 子句中使用多個(gè)條件時(shí)就會(huì)感到困惑了。
我總結(jié)了一種簡(jiǎn)單的方式來(lái)理解上述情況：
將 IS NULL 作為否定匹配條件
使用 !(A and B) == !A OR !B 邏輯判斷
看看下面的示例：
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 匹配子句：
(a.id=b.id) AND (b.weight!=44) AND (b.exist=0)
我們可以把 IS NULL 子句 看作是否定匹配條件。
這意味著我們將檢索到以下行：
!( 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語(yǔ)言中的邏輯 AND 和 邏輯 OR表達(dá)式一樣，其操作數(shù)是從左到右求值的。如果第一個(gè)參數(shù)做夠判斷操作結(jié)果，那么第二個(gè)參數(shù)便不會(huì)被計(jì)算求值（短路效果）
看看別的示例：
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 之戰(zhàn)
如果你吧基本的查詢(xún)條件放在 ON 子句中，把剩下的否定條件放在 WHERE 子句中，那么你會(huì)獲得相同的結(jié)果。
例如，你可以不這樣寫(xiě)：
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;
你可以這樣寫(xiě)：
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> 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)
你可以不這樣寫(xiě)：
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;
可以這樣寫(xiě)：
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> 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)
這些查詢(xún)真的效果一樣？
如果你只需要第一個(gè)表中的數(shù)據(jù)的話(huà)，這些查詢(xún)會(huì)返回相同的結(jié)果集。有一種情況就是，如果你從 LEFT JOIN的表中檢索數(shù)據(jù)時(shí)，查詢(xún)的結(jié)果就不同了。
如前所屬，WHERE 子句是在匹配階段之后用來(lái)過(guò)濾的。
例如：
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)
總附注：
如果你使用 LEFT JOIN 來(lái)尋找在一些表中不存在的記錄，你需要做下面的測(cè)試：WHERE 部分的 col_name IS NULL(其中 col_name 列被定義為 NOT NULL)，MYSQL 在查詢(xún)到一條匹配 LEFT JOIN 條件后將停止搜索更多行（在一個(gè)特定的組合鍵下）。
相關(guān)文章
mysql刪除外鍵抱錯(cuò)Error on rename of ./ruler/test2child to ./ruler/#sql2-298-92 (errno: 152)，怎么回事，有好的方法可以解決嗎？下面看下腳本之家小編給大家?guī)?lái)的解決思路2016-08-08
外鍵可以保持?jǐn)?shù)據(jù)一致性,完整性,主要目的是控制存儲(chǔ)在外鍵表中的數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于Mysql添加外鍵的兩種方式,需要的朋友可以參考下2023-04-04
今天小編就為大家分享一篇關(guān)于想取消錯(cuò)誤的mysql命令怎么辦?，小編覺(jué)得內(nèi)容挺不錯(cuò)的，現(xiàn)在分享給大家，具有很好的參考價(jià)值，需要的朋友一起跟隨小編來(lái)看看吧2019-04-04
下面小編就為大家?guī)?lái)一篇mysql 導(dǎo)出CSV文件 并帶表頭的方法。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-04-04
在MySQL中,有時(shí)候我們需要遠(yuǎn)程連接到服務(wù)器來(lái)進(jìn)行操作,但是有時(shí)候,我們會(huì)遇到無(wú)法使用遠(yuǎn)程連接鏈接到MySQL服務(wù)器的情況,本文就介紹了兩種解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-09-09
mysql 字符串長(zhǎng)度計(jì)算實(shí)現(xiàn)代碼(gb2312+utf8)
PHP對(duì)中文字符串的處理一直困擾于剛剛接觸PHP開(kāi)發(fā)的新手程序員。下面簡(jiǎn)要的剖析一下PHP對(duì)中文字符串長(zhǎng)度的處2011-12-12
CentOS下編寫(xiě)shell腳本來(lái)監(jiān)控MySQL主從復(fù)制的教程
這篇文章主要介紹了在CentOS系統(tǒng)下編寫(xiě)shell腳本來(lái)監(jiān)控主從復(fù)制的教程,文中舉了兩個(gè)發(fā)現(xiàn)故障后再次執(zhí)行復(fù)制命令的例子,需要的朋友可以參考下2015-12-12
用批處理實(shí)現(xiàn)自動(dòng)備份和清理mysql數(shù)據(jù)庫(kù)的代碼
有網(wǎng)友問(wèn)我在win2003下如何自動(dòng)備份MySQL數(shù)據(jù)庫(kù)，既然是自動(dòng)備份，那肯定得寫(xiě)腳本，當(dāng)然我們也可以利用軟件實(shí)現(xiàn)2013-08-08
innodb_flush_method取值方法(實(shí)例講解)
下面小編就為大家?guī)?lái)一篇innodb_flush_method取值方法(實(shí)例講解)。小編覺(jué)得挺不錯(cuò)的，現(xiàn)在就分享給大家，也給大家做個(gè)參考。一起跟隨小編過(guò)來(lái)看看吧2017-03-03
使用Grafana+Prometheus監(jiān)控mysql服務(wù)性能
這篇文章主要介紹了使用Grafana+Prometheus監(jiān)控mysql服務(wù)性能的相關(guān)知識(shí)，本文給大家介紹的非常詳細(xì)，對(duì)大家的工作或?qū)W習(xí)具有一定的參考借鑒價(jià)值,需要的朋友可以參考下方法2020-03-03