oracle 具有外部联接的条件

c9x0cxw0 于 2023-04-11 发布在 Oracle

关注(0)|答案(2)|浏览(183)

在where子句中，我希望返回CRSE_NUMB末尾带有“E”的所有记录，或者返回ASCD_CODE中带有“AS”代码的所有记录。
如果SLRRASG表有一个外部连接，在where语句中添加这个OR条件是否正确？

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

oracle

来源：https://stackoverflow.com/questions/75979885/conditions-with-outer-join

2条答案

按热度按时间

5fjcxozz1#

是的，这完全可以在WHERE子句中进行过滤。只要确保您包含一个条件，用于处理由于外部连接而导致的NULL值，如果您的逻辑需要它。您可能不需要它：

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

如果SLRRASG_ASCD_CODE为NULL，则第二个过滤器将失败，但第一个过滤器仍可能成功，这可能是您想要的，在这种情况下，您可以。但是如果您想将NULL视为与'AS'相同，则应该添加：

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

赞(0）回复(0）举报 2023-04-11

m528fe3b2#

您希望将筛选器添加到联接条件：

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
          AND t2.ascd_code = 'AS'
WHERE  other_conditions

如果您用途：

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
WHERE  t2.ascd_code = 'AS'
AND    other_conditions

那么t2必须有一个匹配的行才能有一个匹配过滤器的ascd_code，你的查询实际上会有一个INNER JOIN而不是OUTER JOIN，所以是一样的：

SELECT *
FROM   table1 t1
       INNER JOIN table2 t2
       ON     t1.id = t2.id
WHERE  t2.ascd_code = 'AS'
AND    other_conditions

如果你确实想在WHERE子句中进行过滤，那么你需要检查OUTER JOIN中的行是否不匹配，你可以通过检查连接条件中使用的列是否为NULL来做到这一点：

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
WHERE  (t2.id IS NULL OR t2.ascd_code = 'AS')
AND    other_conditions

如果你想在多个条件上过滤，并且如果外部连接匹配，它们都必须为true，那么：

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
          AND t2.ascd_code = 'AS'
       LEFT OUTER JOIN table3 t3
       ON     t1.id = t3.id
          AND t3.crse_numb NOT LIKE '%E'
WHERE  other_conditions

或者，如果你想要：t2.ascd_code = 'AS'和t3.crse_numb可以是任何值;或者t2.ascd_code是任意值，并且t3.crse_numb不以E结尾，则：

SELECT *
FROM   table1 t1
       LEFT OUTER JOIN table2 t2
       ON     t1.id = t2.id
       LEFT OUTER JOIN table3 t3
       ON     t1.id = t3.id
WHERE  other_conditions
AND    (  t2.ascd_code = 'AS'
       OR t3.crse_numb NOT LIKE '%E' )

注意：这将强制t2或t3始终匹配，无论哪个匹配的表都将有效地被INNER JOIN ed，另一个表可以被OUTER JOIN ed。

赞(0）回复(0）举报 2023-04-11

我来回答

oracle 具有外部联接的条件

2条答案

相关问题

热门标签

最新问答