3. join操作时，过滤条件写在on和where的异同？

目录结构：

- - 先说结论
  - - left join
    - right join
    - inner join
  - 参考：

先说结论

left join 和 right join 场景中，过滤条件写在on和where有区别。
inner join 场景中，过滤条件写在on和where无区别。

left join

原理：
数据库在通过连接两张或多张表返回数据时，均会先生成一张中间的临时表，然后再将临时表返回给用户。
在使用left join时，on 和 where 条件的区别如下：

on条件是在生成临时表时使用的条件。注意：不管on中的条件是否为真，均会返回左边表中的所有记录。(left join的特性)
where条件是在临时表生成之后，对临时表进行过滤的操作。即在临时表的基础上，条件不为真的全部过滤掉。

示例：

-- 示例数据
with tab1 as (
    select 1 as id, 10 as size union all
    select 2 as id, 20 as size union all
    select 3 as id, 30 as size
), tab2 as (
    select 10 as size, 'AAA' as name union all
    select 20 as size, 'BBB' as name union all
    select 20 as size, 'CCC' as name
)

-- 将tab2的过滤条件写在where中
select
    *
from tab1 left join tab2
on tab1.size=tab2.size
where tab2.name='BBB';
-- 	id	tab1.size	tab2.size	name
--  2	20	        20	        BBB
-- 执行流程: 先关联，再过滤。


-- 将tab2的过滤条件写在on中
select
    *
from tab1 left join tab2
on tab1.size=tab2.size
and tab2.name='BBB'; -- 过滤条件为次表
-- 	id	tab1.size	tab2.size	name
--  1   10          null        null
--  2   20          20         'BBB'
--  3   30          null        null
-- 理解: 先将tab2.name='BBB'的记录筛选出来；再去和tab1做关联。

right join

示例：

-- 示例数据
with tab1 as (
    select 1 as id, 10 as size union all
    select 2 as id, 20 as size union all
    select 2 as id, 30 as size
), tab2 as (
    select 10 as size, 'AAA' as name union all
    select 20 as size, 'BBB' as name union all
    select 20 as size, 'BBB' as name union all
    select 20 as size, 'CCC' as name
)

select
    *
from tab1 right join tab2
on tab1.size=tab2.size
where tab2.name='BBB';
--     id    tab1.size    tab2.size    name
--     2     20           20           'BBB'
--     2     20           20           'BBB'


select
    *
from tab1 right join tab2
on tab1.size=tab2.size
and tab2.name='BBB'; -- 特殊情况(过滤条件为主表)
--      id    tab1.size    tab2.size    name
--	    null  null	       10	        AAA
--      2	  20	       20	        BBB
--      2	  20	       20	        BBB
--      null  null	       20	        CCC
-- 解释: 特别注意结果数据的条数，因为tab2作为主表，所以tab2的所有数据均需返回。

inner join

示例：

-- 示例数据
with tab1 as (
    select 1 as id, 10 as size union all
    select 2 as id, 20 as size union all
    select 2 as id, 30 as size
), tab2 as (
    select 10 as size, 'AAA' as name union all
    select 20 as size, 'BBB' as name union all
    select 20 as size, 'BBB' as name union all
    select 20 as size, 'CCC' as name
)
select
    *
from tab1 inner join tab2
on tab1.size=tab2.size
where tab2.name='BBB';
--     id    tab1.size    tab2.size    name
--     2     20           20           'BBB'
--     2     20           20           'BBB'


select
    *
from tab1 right join tab2
on tab1.size=tab2.size
and tab2.name='BBB';
--     id    tab1.size    tab2.size    name
--     2     20           20           'BBB'
--     2     20           20           'BBB'