深入理解LEFTJOIN

1、深入理解LEFTJOINa表，b表如下*3cccunt_i.dchamlact2lbact3fb=*date_JkeyacGOiint._idchannelspend:20L90B01actlfb50.0D00SQL執(zhí)行順序，先執(zhí)行join,得到一個(gè)join的組合結(jié)果，然后執(zhí)行where,where對(duì)join的整體結(jié)果進(jìn)行過濾。leftjoin的執(zhí)行結(jié)果：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相同字段來自不同表的結(jié)果并不相同，所以在用where做篩選時(shí)應(yīng)注意相同字段表的選擇。幾種連接方式的比較：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是對(duì)leftjoin后的所有結(jié)果進(jìn)行篩選，將篩選所有滿足date_key條件的，而date_key只有b表存在，所以只會(huì)對(duì)b表進(jìn)行篩選，對(duì)a表只會(huì)篩選與b

6、表存在相同account_id的，相當(dāng)于使用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為空時(shí)返回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中不存在的數(shù)據(jù)，貝Uwhere過濾條件中不能使用來自b表的，因?yàn)閎表中數(shù)據(jù)有缺失，將只過濾出b表中b.channel存在的數(shù)據(jù)；只存在于b表而a表沒有的字段也不能使用，將只過濾出b表中該字段存在的數(shù)據(jù)?？偨Y(jié)：1注意相同字段表的選擇，w