当前位置:Gxlcms > 数据库问题 > SQL夯实基础(二):连接操作中使用on与where筛选的差异

SQL夯实基础(二):连接操作中使用on与where筛选的差异

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

  在连接查询语法中,另人迷惑首当其冲的就要属on筛选和where筛选的区别了,如果在我们编写查询的时候, 筛选条件的放置不管是在on后面还是where后面, 查出来的结果总是一样的, 既然如此,那为什么还要多此一举的让sql查询支持两种筛选器呢?  事实上, 这两种筛选器是存在差别的,只是如果不深挖不容易发现而已。

  sql中的连接查询分为3种, cross join,inner join,和outer join, 在 cross join和inner join中,筛选条件放在on后面还是where后面是没区别的,极端一点,在编写这两种连接查询的时候,只用on不使用where也没有什么问题。因此,on筛选和where筛选的差别只是针对outer join,也就是平时最常使用的left join和right join。

二、left join使用on与where筛选的差异

举例进行说明(案例使用mysql),我们现在有两个表,即商品表(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);

 

技术分享图片

  2、测试

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

    2.1使用where条件查询:

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

结果:

pname pcode saletime total

商品1 AC90 2008-09-23 2

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

备注:

查询中用到的关键词主要包含六个,并且他们的顺序依次为

select--from--where--group by--having--order by

其中select和from是必须的,其他关键词是可选的,这六个关键词的执行顺序

与sql语句的书写顺序并不是一样的,而是按照下面的顺序来执行

from--where--group by--having--select--order by,

left join 是在from范围类所以 先on条件筛选表,然后两表再做left join。

而对于where来说在left join结果再次筛选。

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列,或列的计算结果

order by :按照什么样的顺序来查看返回的数据

 

其中from后面的表关联,是自右向左解析的

也就是说,在写SQL文的时候,尽量把数据量小的表放在最右边来进行关联(用小表去匹配大表),

而把能筛选出小量数据的条件放在where语句的最左边 (用小表去匹配大表)

    2.2使用on条件查询:

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

结果:

pname pcode saletime total

商品1 AC90 2009-09-23 2

商品2 DE78 NULL 0

商品3 XXXX NULL 0

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

SELECT
p.pname,
p.pcode,
s.saletime,
count(s.aid) AS total
FROM
products AS p
LEFT 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.pcode
ORDER BY
total DESC,
p.pid ASC

三、结论

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

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

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

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

 

SQL夯实基础(二):连接操作中使用on与where筛选的差异

标签:har   解析   -o   des   基础   body   两种   gpo   临时表   

人气教程排行