当前位置:Gxlcms > mysql > leftjoin里面的and与where区别

leftjoin里面的and与where区别

时间:2021-07-01 10:21:17 帮助过:39人阅读

无详细内容 无 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) NO

<无详细内容> <无>
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');
查出来的结果:
+-------+-------+------------+
| 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');

查询出来的结果
+-------+-------+------------+
| pname | pcode | saletime   |
+-------+-------+------------+
| A     | AC    | 2012-07-23 |
| A     | AC    | 2012-07-05 |
| A     | AC    | 2012-07-05 |
+-------+-------+------------+

结论:on中的条件关联,一表数据不满足条件时会显示空值。where则
输出两表完全满足条件数据。 我的理解: left join里面的条件:会以左表的基准数据,凡左表出现的数据均要出现,然后再进行join右表, 只要关联上的就需要查出来,如果相应的字段没有值或不符合条件的话就置为NULL。 SELECT p.pname,p.pcode,s.saletime from product as p left join sales_detail as s on (s.pcode=p.pcode) ;光左连接的话显示的内容如下 +-------+-------+------------+ | pname | pcode | saletime | +-------+-------+------------+ | A | AC | 2012-07-23 | | A | AC | 2012-07-05 | | A | AC | 2012-07-05 | | B | DE | 2012-07-16 | 这里面有值 | C | XXX | NULL | 这里面没有值 +-------+-------+------------+ 有值但是不符合条件的话就置为NULL。如果没有值肯定为NULL 如果是where条件的话就肯定是要满足才行。 应用场景:比如有个主表,那以主表为基准去显示数据可以考虑left join的方式处理

人气教程排行