深入理解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 示例：
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 子句有什麼不同？
一個問題：下面兩個查詢的結果集有什麼不同麼？
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> 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的資料中過濾掉不符合條件的資料行。
再來看一些示例：
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 條件並沒有匹配到任何資料）
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 邏輯判斷
看看下面的示例：
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語言中的邏輯 AND 和 邏輯 OR表示式一樣，其運算元是從左到右求值的。如果第一個引數做夠判斷操作結果，那麼第二個引數便不會被計算求值（短路效果）
看看別的示例：
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 子句中，那麼你會獲得相同的結果。
例如，你可以不這樣寫：
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;
你可以這樣寫：
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)
你可以不這樣寫：
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;
可以這樣寫：
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)
這些查詢真的效果一樣？
如果你只需要第一個表中的資料的話，這些查詢會返回相同的結果集。有一種情況就是，如果你從 LEFT JOIN的表中檢索資料時，查詢的結果就不同了。
如前所屬，WHERE 子句是在匹配階段之後用來過濾的。
例如：
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 來尋找在一些表中不存在的記錄，你需要做下面的測試：WHERE 部分的 col_name IS NULL(其中 col_name 列被定義為 NOT NULL)，MYSQL 在查詢到一條匹配 LEFT JOIN 條件後將停止搜尋更多行（在一個特定的組合鍵下）。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4729/viewspace-2810305/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql + left joinMySql
- MySQL 之 LEFT JOIN 避坑指南MySql
- sql的left join 命令詳解SQL
- 深入Oracle的left join中on和where的區別詳解Oracle
- mysql left join轉inner joinMySql
- MySQL LEFT JOIN/ INNER JOIN/RIGHT JOINMySql
- mysql中的left join、right join 、inner join的詳細用法MySql
- 【MySQL】LEFT JOIN 踩坑MySql
- mysql join詳解MySql
- mysql left join 優化學習MySql優化
- sql left join 和 right join解釋SQL
- sql之left join、right join、inner join的區別SQL
- MYSQL 怎麼 LEFT JOIN 多表聯查MySql
- sql的left join 、right join 、inner join之間的區別SQL
- MYSQL count標量子查詢改left joinMySql
- MySql的join（連線）查詢 （三表 left join 寫法）MySql
- Hive JOIN使用詳解Hive
- SQL Server Left joinSQLServer
- LEFT JOIN 和JOIN 多表連線
- sql：left join和join區別SQL
- sql中的join、left join、right joinSQL
- join、inner join、left join、right join、outer join的區別
- mysql的left join和inner join的效率對比，以及如何優化MySql優化
- mysql常用連線查詢join，left,right,crossMySqlROS
- Mysql-left join on後接and，和where的區別MySql
- 深入理解MySQL系列之鎖MySql
- 深入理解MySQL系列之索引MySql索引
- 連線查詢簡析 join 、 left join 、 right join
- mysql INNER JOIN、LEFT JOIN、RIGHT JOIN；內連線（等值連線）、左連線、右連線MySql
- oracle update left join查詢Oracle
- Oracle Left join right jionOracle
- sql server left join問題SQLServer
- Blocks深入理解和詳解BloC
- Inner Join, Left Outer Join和Association的區別
- SQL中聯表查詢操作（LEFT JOIN, RIGHT JOIN, INNER JOIN）SQL
- MySQL筆記-左連線的使用（left join有關聯的多表查詢）MySql筆記
- Oracle -- left join 什麼情況可以直接改成joinOracle
- 深入理解Emoji(三) —— Emoji詳解