外连接场景下WHERE子句添加OR条件的合理性咨询

问题描述 #

我希望在WHERE子句中返回所有CRSE_NUMB结尾为'E'的记录，或者所有ASCD_CODE为'AS'的记录。请问在SLRRASG表使用左外连接的情况下，在WHERE语句中添加该OR条件是否可行？

原始SQL代码 #

select distinct
      SPRIDEN.SPRIDEN_PIDM PIDM,
      SFRSTCR.SFRSTCR_TERM_CODE Term_Code, 
      SFRSTCR.SFRSTCR_PTRM_CODE,
      SPRIDEN.SPRIDEN_LAST_NAME LNAME, 
      SPRIDEN.SPRIDEN_FIRST_NAME FNAME, 
      SPRIDEN.SPRIDEN_ID SID,
      SGBSTDN.SGBSTDN_COLL_CODE_1,
     -- decode(SGBSTDN.SGBSTDN_COLL_CODE_1, 'YC', 'Yale_College', 'SU', 'Visiting_Student') Student_Type, 
   --   SFRSTCR.SFRSTCR_RSTS_CODE, 
   --   SGBSTDN.SGBSTDN_LEVL_CODE LVL_Code, 
   --   SFRSTCR.SFRSTCR_LEVL_CODE CRS_LVL, 
       SLRRASG.SLRRASG_ASCD_CODE Housing_Status,
    -- SLRRASG.SLRRASG_BEGIN_DATE, 
    -- SLRRASG.SLRRASG_END_DATE,
      SCBCRSE.SCBCRSE_SUBJ_CODE, 
      SCBCRSE.SCBCRSE_CRSE_NUMB,
      SFRSTCR.SFRSTCR_CRN CRN,
    --  SCBCRSE.SCBCRSE_TITLE,
    --  SFRSTCR.SFRSTCR_ADD_DATE,
    --  SCBCRSE.SCBCRSE_OTH_HR_LOW as "Credit",
    --  SFRSTCR.SFRSTCR_BILL_HR, 
    --  SFRSTCR.SFRSTCR_CREDIT_HR
    --SFRSTCR.SFRSTCR_CREDIT_HR, 
    listagg(distinct goremal.goremal_email_address, ',')
from
  SATURN.SGBSTDN
  join SATURN.SFRSTCR
    on SGBSTDN.SGBSTDN_PIDM = SFRSTCR.SFRSTCR_PIDM
      and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SFRSTCR.SFRSTCR_TERM_CODE
  join SATURN.SPRIDEN
    on SGBSTDN.SGBSTDN_PIDM = SPRIDEN.SPRIDEN_PIDM
  left join SATURN.SLRRASG
      on SGBSTDN.SGBSTDN_PIDM = SLRRASG.SLRRASG_PIDM
         and SGBSTDN.SGBSTDN_TERM_CODE_EFF = SLRRASG.SLRRASG_TERM_CODE
         -- and  SLRRASG.SLRRASG_ASCD_CODE = 'AS'
 join SATURN.SSBSECT
  on SFRSTCR.SFRSTCR_TERM_CODE = SSBSECT.SSBSECT_TERM_CODE
    and SFRSTCR.SFRSTCR_CRN = SSBSECT.SSBSECT_CRN
 join SATURN.SCBCRSE
  on SSBSECT.SSBSECT_SUBJ_CODE = SCBCRSE.SCBCRSE_SUBJ_CODE
    and SSBSECT.SSBSECT_CRSE_NUMB = SCBCRSE.SCBCRSE_CRSE_NUMB
  left join GENERAL.GOREMAL
   on SPRIDEN.SPRIDEN_PIDM = GOREMAL.GOREMAL_PIDM
    and GOREMAL.GOREMAL_EMAL_CODE = 'HAPP'
where
  SPRIDEN.SPRIDEN_ID like '9%'
  and  SFRSTCR.SFRSTCR_TERM_CODE = '202302'
  and SGBSTDN.SGBSTDN_COLL_CODE_1 = 'SU'
  and SPRIDEN.SPRIDEN_CHANGE_IND is null
  and SFRSTCR.SFRSTCR_RSTS_CODE in ('RE', 'RW')
  and SSBSECT.SSBSECT_SSTS_CODE in ('A', 'E', 'O')
  and SFRSTCR.SFRSTCR_PTRM_CODE in ('H4B', 'H5A', 'H5B', 'H10', 'H8', 'HSL', 'HND', 'HE3', 'HEL', 'H6', 'HFY', 'HWY')
  and (SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E' or 
      SLRRASG.SLRRASG_ASCD_CODE = 'AS')
 --  and SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E'
  and SCBCRSE.SCBCRSE_EFF_TERM = (
    select max(scbcrse_eff_term) from saturn.scbcrse xppd
    where
    xppd.scbcrse_subj_code = scbcrse.scbcrse_subj_code
    and
    xppd.scbcrse_crse_numb = scbcrse.scbcrse_crse_numb and xppd.scbcrse_eff_term <= ssbsect_term_code)
group by
   SPRIDEN.SPRIDEN_PIDM,
  SFRSTCR.SFRSTCR_TERM_CODE, 
  SFRSTCR.SFRSTCR_PTRM_CODE,
  SPRIDEN.SPRIDEN_LAST_NAME, 
  SPRIDEN.SPRIDEN_FIRST_NAME, 
  SPRIDEN.SPRIDEN_ID,
  SGBSTDN.SGBSTDN_COLL_CODE_1,
   SLRRASG.SLRRASG_ASCD_CODE,
 SLRRASG.SLRRASG_BEGIN_DATE, 
 SLRRASG.SLRRASG_END_DATE,
  SCBCRSE.SCBCRSE_SUBJ_CODE, 
  SCBCRSE.SCBCRSE_CRSE_NUMB,
 SFRSTCR.SFRSTCR_CRN

解答 #

可行，但要注意左外连接的NULL值特性以及当前条件的逻辑错误：

1. 修正条件逻辑：你当前写的是SCBCRSE.SCBCRSE_CRSE_NUMB not like '%E'，和需求的「结尾为'E'」完全相反，应该改成：

   and (SCBCRSE.SCBCRSE_CRSE_NUMB like '%E' or SLRRASG.SLRRASG_ASCD_CODE = 'AS')
   

2. 左外连接的NULL影响：因为SLRRASG是左外连接，当没有匹配的记录时，SLRRASG.SLRRASG_ASCD_CODE会是NULL，此时SLRRASG.SLRRASG_ASCD_CODE = 'AS'会判定为false，不会干扰「CRSE_NUMB结尾为E」的记录筛选，符合你的需求。

3. 可选优化点：

   • 如果想提前过滤SLRRASG的匹配行，可以把SLRRASG.SLRRASG_ASCD_CODE = 'AS'移到LEFT JOIN的ON子句中，但这会改变逻辑：移到ON子句会保留所有主表记录，仅过滤SLRRASG的匹配行；而放在WHERE子句是在连接后过滤整个结果集。
   • 当前GROUP BY中包含了SLRRASG.SLRRASG_BEGIN_DATE和SLRRASG.SLRRASG_END_DATE，但SELECT里没有这两个字段，会导致分组不必要的细化，建议要么从GROUP BY中移除，要么添加到SELECT列表里。

内容的提问来源于stack exchange，提问作者RTC