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、