使用DBMS连接2张或多张表来做查询时, 均会生成一张中间临时表, 然后对临时表做过滤筛选后, 将结果集返回给客户端.

这里以左连接为例, on 和 where, having做条件筛选时, 区别如下:

  1. left join中如果使用了on, 如果on条件为true, 则将过滤掉这部分数据后(如右表user.sex=‘男’), 再与左表进行关联. on条件是在生成临时表时使用的条件, 无论如果on条件为ture, 或是false, 都将会返回左表中所有的记录.
  2. 而where条件则是在临时表生成后(包括on中过滤部分数据之后), 再对临时表的结果集进行再过滤. 因此时其为一个完整的结果集, 无左表或右表之分, 当条件不为真时, 不符合的结果期就将全部被过滤掉.
  3. having的执行次序, 会排在 on 和 where条件 之后. 因为次序的问题,其总是最后被执行.

开发过程中的最优实践:

  1. 这几类过滤条件中, on是先把不符合条件的记录过滤后才进行统计, 在某些字段可以过滤掉相当多数据的应用场景中, 可以减少中间计算所需要处理的数据量, 对sql的优化也最明显.
  2. 只有两张以上表的关联场景中,才会用到on, 而一张表的查询, 只有where与having.
  3. 执行完where字句的过滤功能后, 才能得到过滤后的数据集, 过滤数据后才能执行sum/count等聚合操作, 故where将可能比having更快执行.
  4. 多表join的场景中, on条件 比 where条件 更早起作用, dbms先根据各表之间的on条件, 将多个表连接为一个临时表后, 再使用where条件进行过滤, 涉及到聚合计算(如group by等)后, 再使用having进行最后的过滤. 根据这个时间线, 如果想让各种过滤条件放在最合适的位置, 则需要开发者熟练掌握每种过滤条件的过滤机制与过滤时机, 做到因地制宜.

案例1:

  • 左表关联右表, 根据size字段进行关联, 最后过滤出只剩name='AAA’的记录.
-- 左表关联右表, 根据size字段进行关联, 最后过滤出只剩name='AAA'的记录.
select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA';

sql运行结果:
id	size	size1	name
1	10	10	AAA

案例2:

  • 左表关联右表, 在on条件中根据size字段进行关联, 并同时过滤出只剩name='AAA’的记录.这里没有where条件字句.
-- 实现方式①:
----- 在on字句中同时关联连接字段  及  过滤符合条件的name字段.
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA');


-- 实现方式②:
-----先将非多表关联字段,使用子查询获得结果集, 然后与左表进行关联, 并为on条件指定关联字段, 因on条件无论true 或false, 都会返回左边所有的字段, 则左边3条记录都将得到返回. 右表中, tab2中匹配到name的字段得以保留,其他tab2中未匹配的字段都将以null值填充.
select * from tab1 left join 
(select * from tab2 where tab2.name='AAA') tab2
 on (tab1.size = tab2.size);

sql运行结果:
id	size	size1	name
1	10	10	AAA
2	20	null	null	
3	30	null	null

参考列表:
-runoob.com