深入理解LEFTJOIN

1、深入理解LEFTJOINa表，b表如下*3cccunt_i.dchamlact2lbact3fb=*date_JkeyacGOiint._idchannelspend:20L90B01actlfb50.0D00SQL执行顺序，先执行join,得到一个join的组合结果，然后执行where,where对join的整体结果进行过滤。leftjoin的执行结果：selecta.account_idasa_account_id,a.channelasa_channel,b.account_idasb_account_id,b.channelasb_channel,b.date_key,b.spendf

2、romaleftjoinbonb.account_id=a.account_ida_account_ida_chaimelb_account_idb_channeldate_keyspend1act3fbact3fb20190603150,00002sctlfbactl2019080150.0000相同字段来自不同表的结果并不相同，所以在用where做筛选时应注意相同字段表的选择。几种连接方式的比较：1.where条件选用a.channelselecta.account_idasa_account_id,a.channelasa_channel,b.account_idasb_account_

3、id,b.channelasb_channel,b.date_key,b.spendfromaleftjoinbonb.account_id=a.account_idwherea.channel=fb耳a_aceDunl_ida_chatmlb_aecouni_idb_channeldaie_keyspend1act3fbact3fb20190303150.00002actlftactlfb2019080150.00002.where条件选用b.channelselecta.account_idasa_account_id,a.channelasa_channel,b.account_idas

4、b_account_id,b.channelasb_channel,b.date_key,b.spendfromaleftjoinbonb.account_id=a.account_idwhereb.channel=fb*a_accouiit_ica_chaniBlb_accaunl_idb_channeldatc_keyspend:actSfbact3tb20190303150.0000where条件选用a.channel,b.date_key写在where中selecta.account_idasa_account_id,a.channelasa_channel,b.account_ida

5、sb_account_id,b.channelasb_channel,b.date_key,b.spendfromaleftjoinbonb.account_id=a.account_idwherea.channel=fbanddate_key=20190801*3_accoLint_ida_ch3imsLb_accoimt_idb_chanr：91date_kevspend1actlfbactlfb20190801EiO.0000where是对leftjoin后的所有结果进行筛选，将筛选所有满足date_key条件的，而date_key只有b表存在，所以只会对b表进行筛选，对a表只会筛选与b

6、表存在相同account_id的，相当于使用join。where条件选用b.channel,b.date_key写在where中selecta.account_idasa_account_id,a.channelasa_channel,b.account_idasb_account_id,b.channelasb_channel,b.date_key,b.spendfromaleftjoinbonb.account_id=a.account_idwhereb.channel=fbanddate_key=20190801耳a_accDunl_ida_channelb_3CC0UHL_idb_ch

7、anneldare_keyspend:3Cl3fbact3ft20190S03150.0000b.date_key写在leftjoin条件中,where条件选用a.channel（只有这一种写法可以得出spend为空时返回account_id）selecta.account_idasa_account_id,a.channelasa_channel,b.account_idasb_account_id,b.channelasb_channel,b.date_key,b.spendfromaleftjoinbonb.account_id=a.account_idanddate_key=20190

8、801wherea.channel=fb*a_accDunt_ida_channelb_account_idb_channeldatekeyspend:act3fbaci3ft20190S03150.00002&ctlactlfb2019030150.GOODb.date_key写在leftjoin条件中，where条件选用b.channelselecta.account_idasa_account_id,a.channelasa_channel,b.account_idasb_account_id,b.channelasb_channel,b.date_key,b.spendfromaleftjoinbonb.account_id=a.account_idanddate_key=20190801whereb.channel=fb*a_accoint_ia_zhannElb_cco_mt_id_chameldate_keyspaid1acLdfbfb2319082315C.DOOC如果想要得到a中存在而b中不存在的数据，贝Uwhere过滤条件中不能使用来自b表的，因为b表中数据有缺失，将只过滤出b表中b.channel存在的数据；只存在于b表而a表没有的字段也不能使用，将只过滤出b表中该字段存在的数据。总结：1注意相同字段表的选择，w