深入理解LEFT JOIN

a表，b表如下

SQL执行顺序，先执行join，得到一个join的组合结果，然后执行where,where对join的整体结果进行过滤。

left join的执行结果：

select a.account_id as a_account_id
        ,a.channel as a_channel
         ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
;

相同字段来自不同表的结果并不相同，所以在用where做筛选时应注意相同字段表的选择。

几种连接方式的比较：

1.where条件选用a.channel

select  a.account_id as a_account_id
        ,a.channel as a_channel
        ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
where a.channel = 'fb'
;

2.where条件选用b.channel

select a.account_id as a_account_id
        ,a.channel as a_channel
         ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
where b.channel = 'fb'

3.where条件选用a.channel，

   b.date_key写在where中

select a.account_id as a_account_id
        ,a.channel as a_channel
         ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
where a.channel = 'fb'
and date_key >= 20190801
;

where是对left join后的所有结果进行筛选，将筛选所有满足date_key条件的，而date_key只有b表存在，所以只会对b表进行筛选，对a表只会筛选与b表存在相同account_id的，相当于使用join。

4.where条件选用b.channel,

  b.date_key写在where中

select a.account_id as a_account_id
        ,a.channel as a_channel
         ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
where b.channel = 'fb'
and date_key >= 20190801
;

5.b.date_key写在left join条件中，

   where条件选用a.channel

（只有这一种写法可以得出spend为空时返回account_id）

select a.account_id as a_account_id
        ,a.channel as a_channel
         ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
and date_key >= 20190801
where a.channel = 'fb'
;

6.b.date_key写在left join条件中，

    where条件选用b.channel

select a.account_id as a_account_id
        ,a.channel as a_channel
         ,b.account_id as b_account_id
        ,b.channel as b_channel
        ,b.date_key
        ,b.spend
from a
left join b on b.account_id = a.account_id
and date_key >= 20190801
where b.channel = 'fb'
;

 

如果想要得到a中存在而b中不存在的数据，则where过滤条件中不能使用来自b表的，因为b表中数据有缺失，将只过滤出b表中b.channel存在的数据；

只存在于b表而a表没有的字段也不能使用，将只过滤出b表中该字段存在的数据。

总结：

1）注意相同字段表的选择，where中使用a.channel和b.channel是不同的结果。

2）注意条件(date_key)是放在left join中，还是放在where中，放在left join中可得出与a表有相同字段(account_id)的所有结果，放在where中只筛选符合(date_key)条件的。