mysql left join的on和where的差异

1、LEFT JOIN 的条件过滤规则（ON和WHERE的条件过滤差异）

1）基础表信息
SELECT * FROM product;

+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    200 |
|  3 |    300 |
|  4 |    400 |

SELECT * FROM product_details;

+----+--------+-------+
| id | weight | exist |
+----+--------+-------+
|  2 |     22 |     0 |
|  4 |     44 |     1 |
|  5 |     55 |     0 |
|  6 |     66 |     1 |
+----+--------+-------+

左连接left join on。返回记录数与A表记录数一致，无论A/B表是否能匹配上。
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 |
+----+--------+------+--------+-------+

2）左连接中，on条件与where条件过滤的差异
  “A LEFT JOIN B ON 条件表达式”，ON 条件（“A LEFT JOIN B ON 条件表达式”中的ON）用来决定如何从 B 表中检索数据行。如果 B 表中没有任何一行数据匹配 ON 的条件,将会额外生成一行所有列为 NULL 的数据
  在匹配阶段 WHERE 子句的条件都不会被使用。仅在匹配阶段完成以后，WHERE 子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤。

##使用 ON 条件决定了从 LEFT JOIN的 product_details表中检索符合的所有数据行。
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 |
+----+--------+------+--------+-------+

##LEFT JOIN之后，使用 WHERE 子句从 LEFT JOIN的数据中过滤掉不符合条件的数据行
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 |
+----+--------+----+--------+-------+

3）所有来自product表的数据行都被检索到了，但没有在product_details表中匹配到记录（product.id = product_details.id AND product.amount=100 条件并没有匹配到任何数据）
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 |
+----+--------+------+--------+-------+

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）使用 WHERE ... IS NULL 子句的 LEFT JOIN
##  先过滤on条件获取到B表的结果集，合并到A表，再对结果集过滤where条件。
SELECT * 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 | id   | weight | exist |
+----+--------+------+--------+-------+
|  1 |    100 | NULL |   NULL |  NULL |
|  3 |    300 | NULL |   NULL |  NULL |
|  4 |    400 | NULL |   NULL |  NULL |
+----+--------+------+--------+-------+

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 |
+----+--------+------+--------+-------+

建表语句：

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;
 
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;
 
INSERT INTO product (id,amount) VALUES (1,100),(2,200),(3,300),(4,400);
INSERT INTO product_details (id,weight,exist) VALUES (2,22,0),(4,44,1),(5,55,0),(6,66,1);

转自：http://www.oschina.net/question/89964_65912

2、