leftjoin里面的and與where區(qū)別

CREATE TABLE `product` (
 `pid` int(4) NOT NULL auto_increment,
 `pname` char(20) default NULL,
 `pcode` char(20) default NULL,
 PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



CREATE TABLE `sales_detail` (
 `aid` int(4) NOT NULL auto_increment,
 `pcode` char(20) default NULL,
 `saletime` date default NULL,
 PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `product` VALUES ('1', 'A', 'AC');
INSERT INTO `product` VALUES ('2', 'B', 'DE');
INSERT INTO `product` VALUES ('3', 'C', 'XXX');

INSERT INTO `sales_detail` VALUES ('1', 'AC', '2012-07-23');
INSERT INTO `sales_detail` VALUES ('2', 'DE', '2012-07-16');
INSERT INTO `sales_detail` VALUES ('3', 'AC', '2012-07-05');
INSERT INTO `sales_detail` VALUES ('4', 'AC', '2012-07-05');


left join里面帶and的查詢
SELECT p.pname,p.pcode,s.saletime from product as p left join sales_detail as s on (s.pcode=p.pcode) and s.saletime in ('2012-07-23','2012-07-05');
查出來的結(jié)果：
+-------+-------+------------+
| pname | pcode | saletime |
+-------+-------+------------+
| A | AC | 2012-07-23 |
| A | AC | 2012-07-05 |
| A | AC | 2012-07-05 |
| B | DE | NULL |
| C | XXX | NULL |
+-------+-------+------------+

直接where條件查詢
SELECT p.pname,p.pcode,s.saletime from product as p left join sales_detail as s 
on (s.pcode=p.pcode) where s.saletime in ('2012-07-23','2012-07-05');

查詢出來的結(jié)果
+-------+-------+------------+
| pname | pcode | saletime |
+-------+-------+------------+
| A | AC | 2012-07-23 |
| A | AC | 2012-07-05 |
| A | AC | 2012-07-05 |
+-------+-------+------------+

結(jié)論：on中的條件關(guān)聯(lián)，一表數(shù)據(jù)不滿足條件時會顯示空值。where則