oracle left join 查询右表没有匹配到的数据

oracle left join 查询右表没有匹配到的数据,目前用的是 is null 做的判断,但是效率很慢,各位大神有没有高效一点的方法

select shd.*
from temp_termspastdue tmp join (select s.loan_no,s.ps_due_dt,
loan.cust_id,
loan.loan_grd,
loan.loan_actv_dt,
loan.orig_prcp,
loan.last_due_dt,
loan.OLD_LAST_DUE_DT,
loan.recentpaydt,
loan.typ_rat,
ct.loan_od_grace
from (select loan_no,ps_due_dt from CREDIT_LM_PM_SHD where PP_ER_IND='N' and to_date(ps_due_dt, 'yyyy-MM-dd') >= date '2016-01-01' and (setl_ind<>ps_od_ind or(ps_od_ind='Y' and setl_ind='Y')) ) s
join (select l.loan_no,
l.loan_cont_no,
l.loan_actv_dt,
l.last_due_dt,
l.last_setl_dt recentpaydt,
l.cust_id,
l.loan_grd,
l.Orig_Prcp,
l.OLD_LAST_DUE_DT,
typ.typ_rat
from ods_cmsg_lm_loan l, loan_typ_detail typ
where typ.typ_cde = l.loan_typ
and typ.upl_flg = 1
and typ.typ_rat='2'
and l.loan_sts = 'ACTV') loan
on loan.loan_no = s.loan_no
and s.ps_due_dt > loan.loan_actv_dt
join ods_cmsg_lm_loan_cont ct
on ct.loan_cont_no = loan.loan_cont_no
where to_date(s.ps_due_dt, 'yyyy-MM-dd') + ct.loan_od_grace <i_data_dt
) shd
on tmp.loan_no=shd.loan_no
join s01_tiu_person_all ps
on ps.cust_id = shd.cust_id
left join s01_tiu_trade_all al
on al.account = tmp.loan_no
and to_char(al.billingdate, 'yyyy-MM') =
to_char(to_date(shd.ps_due_dt, 'yyyy-MM-dd'), 'yyyy-MM')
where al.account is null;

1个回答

如尝试把s01_tiu_trade_all放在条件里
AND NOT EXISTS(SELECT 1 FROM s01_tiu_trade_all AS al WHERE al.account = tmp.loan_no and to_char(al.billingdate, 'yyyy-MM') =to_char(to_date(shd.ps_due_dt, 'yyyy-MM-dd'), 'yyyy-MM'))

LvYouQian
LvYouQian 非常感谢!
4 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐