你的位置:首页 > 数据库

[数据库]mysql的left join使用on与where筛选的差异


    举例进行说明,我们现在有两个表,即商品表(products)与sales_detail(销售记录表)。我们主要是通过这两个表来对MySQL关联left join 条件on与where 条件的不同之处进行讲述。

 1、数据准备

创建products表并插入数据

drop table if exists products;  CREATE TABLE `products` (  `pid` INT (3) NOT NULL auto_increment,  `pname` VARCHAR (20) NOT NULL,  `pcode` VARCHAR (20) NOT NULL,  PRIMARY KEY (`pid`)) ENGINE = MyISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;INSERT INTO `products` (`pid`, `pname`, `pcode`)VALUES  (1, '商品1', 'AC90'),  (2, '商品2', 'DE78'),  (3, '商品3', 'XXXX');

 创建sales_detail表并插入数据

drop table if exists sales_detail;CREATE TABLE `sales_detail` (  `aid` INT (3) NOT NULL auto_increment,  `pcode` VARCHAR (20) NOT NULL,  `saletime` date NOT NULL,  PRIMARY KEY (`aid`)) ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1;INSERT INTO `sales_detail` (`aid`, `pcode`, `saletime`)VALUES  (1, 'AC90', '2008-09-22'),  (2, 'DE78', '2008-09-22'),  (3, 'AC90', '2008-09-23'),  (4, 'AC90', '2008-09-24');

 

 数据库中的数据如下:

products表
pidpnamepcode
1商品1AC90
2商品2DE78
3商品3XXXX
sales_detail表
aidpcodesaletime
1AC902008-09-22
2DE782008-09-22
3AC902008-09-23
4AC902008-09-24

2、测试

现在有个场景,按商品在某个时间段内的销售量来排行,比如我想统计23-24号这两天的销售数量并排行。(注:DE78这个商品在这两天没有销售,但是也要显示出来,只是数量为0)

使用where条件查询:

SELECT  p.pname,  p.pcode,  s.saletime,  count(s.aid) AS totalFROM  products AS pLEFT JOIN sales_detail AS s ON (s.pcode = p.pcode)WHERE  s.saletime IN ('2008-09-23', '2008-09-24')GROUP BY  p.pcodeORDER BY  total DESC,  p.pid ASC

结果:

pnamepcodesaletimetotal
商品1AC902008-09-232

 

 

这里的查询过程可以分成两部,首先通过on条件生成中间表(总共有3条数据),然后用where条件过滤中间表得到最后的结果。

使用on条件查询:

SELECT  p.pname,  p.pcode,  s.saletime,  count(s.aid) AS totalFROM  products AS pLEFT JOIN sales_detail AS s ON (  (s.pcode = p.pcode)  AND s.saletime IN ('2008-09-23', '2008-09-24'))GROUP BY  p.pcodeORDER BY  total DESC,  p.pid ASC

结果:

pnamepcodesaletimetotal
商品1AC902009-09-232
商品2DE78NULL0
商品3XXXXNULL0

 

 

 

 

这里直接通过on条件得到结果,不管on上的条件是否为真都会返回left表中的记录,如果需要不满足连接条件的行也出现在查询结果中,必须把连接条件放在on上。以上查询等价于:

SELECT  p.pname,  p.pcode,  s.saletime,  count(s.aid) AS totalFROM  products AS pLEFT JOIN (select * from sales_detail s where s.saletime IN ('2008-09-23', '2008-09-24')) as s ON   (s.pcode = p.pcode)GROUP BY  p.pcodeORDER BY  total DESC,  p.pid ASC

 

 3、结论

  数据库在通过连接两张或多张表来返回记录时,都会生成一张中间的临时表,然后再将这张临时表返回给用户。

  在使用left jion时,on和where条件的区别如下:

1、 on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。