深入理解mysql之left join 使用詳解

更新時間：2012年09月05日 23:09:45 作者：

即使你認(rèn)為自己已對 MySQL 的 LEFT JOIN 理解深刻，但我敢打賭，這篇文章肯定能讓你學(xué)會點(diǎn)東西

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

關(guān)于 “A LEFT JOIN B ON 條件表達(dá)式” 的一點(diǎn)提醒

ON 條件（“A LEFT JOIN B ON 條件表達(dá)式”中的ON）用來決定如何從 B 表中檢索數(shù)據(jù)行。

如果 B 表中沒有任何一行數(shù)據(jù)匹配 ON 的條件,將會額外生成一行所有列為 NULL 的數(shù)據(jù)

在匹配階段 WHERE 子句的條件都不會被使用。僅在匹配階段完成以后，WHERE 子句條件才會被使用。它將從匹配階段產(chǎn)生的數(shù)據(jù)中檢索過濾。

讓我們看一個 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 子句有什么不同？

一個問題：下面兩個查詢的結(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;

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

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表中檢索符合的所有數(shù)據(jù)行。

第二條查詢做了簡單的LEFT JOIN，然后使用 WHERE 子句從 LEFT JOIN的數(shù)據(jù)中過濾掉不符合條件的數(shù)據(jù)行。

再來看一些示例：

所有來自product表的數(shù)據(jù)行都被檢索到了，但沒有在product_details表中匹配到記錄（product.id = product_details.id AND product.amount=100 條件并沒有匹配到任何數(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)

同樣，所有來自product表的數(shù)據(jù)行都被檢索到了，有一條數(shù)據(jù)匹配到了。

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

當(dāng)你使用 WHERE ... IS NULL 子句時會發(fā)生什么呢？

如前所述，WHERE 條件查詢發(fā)生在匹配階段之后，這意味著 WHERE ... IS NULL 子句將從匹配階段后的數(shù)據(jù)中過濾掉不滿足匹配條件的數(shù)據(jù)行。

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

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

將 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表達(dá)式一樣，其操作數(shù)是從左到右求值的。如果第一個參數(shù)做夠判斷操作結(jié)果，那么第二個參數(shù)便不會被計算求值（短路效果）

看看別的示例：

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)

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

例如，你可以不這樣寫：

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)

這些查詢真的效果一樣？

如果你只需要第一個表中的數(shù)據(jù)的話，這些查詢會返回相同的結(jié)果集。有一種情況就是，如果你從 LEFT JOIN的表中檢索數(shù)據(jù)時，查詢的結(jié)果就不同了。

如前所屬，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)