Oracle外连接查询无结果问题：调整条件后才返回数据

问题背景 #

有一条Oracle查询语句存在以下现象：

• 注释掉AND EVT.EVT_EVNT_ID(+) = D1.D1_EVNT_ID后，查询能返回结果
• 给AND EVT.EVT_ENTITY_ID ='G'添加外连接标记(+)后，查询也能返回结果
• 保持原语句不变时，查询无结果

已确认D1.D1_evnt_id='12457886544'在所有相关表中存在，需找出问题根源。原查询语句如下：

select *         
from 
    (select B11.ext_db_id,B11.FRST_LINE_OF_PRFRD_NAME, E11.*, D1.D1_evnt_id,    
        (SELECT E1.E1_DT
           FROM  S1core.E1_EVNT_DT_DTLS E1
          WHERE E1.E1_ENTITY_ID = D1.D1_ENTITY_ID
          AND E1.E1_EVNT_ID     = D1.D1_EVNT_ID
          AND E1.E1_D1_ID      = D1.D1_D1_ID
          AND E1.E1_DT_TYP      = 'MEET'
          AND E1.E1_OPTN_SEQ_N  = '999'
          AND E1.STATUS          = 'AUTHD' ) "MEET_DATE"
         FROM  S1core.E1_EVNT_DT_DTLS E11,  S1core.SECURITY_ACCOUNT SFA,
         S1core.EXTRNL_SYS_DETAILS EXT,
         S1core.D1_DPOT_EVNT_DTLS D1,  S1core.IP_ACNT_RELATION i1,
         S1core.ELG_ELGBLTY E1,S1core.P1_D1_PRXY_DTLS P1, S1core.EVT_DTLS   EVT,
         S1core.BUSINESS_PARTNER B1, S1core.BUSINESS_PARTNER B11
        WHERE
        P1.P1_EVNT_ID(+)     = D1.D1_EVNT_ID                   
        AND B1.BP_ID          = i1.IP_ID
        AND SFA.BP_ID          = B11.BP_ID
        AND B11.OWNER_ENTITY  =  SFA.OWNER_ENTITY
        AND P1.P1_D1_ID(+)   = D1.D1_D1_ID
       AND P1.P1_ENTITY_ID(+) = D1.D1_ENTITY_ID
        AND P1.STATUS(+)        = 'AUTHD'
        AND EXT.LVL_REF    =    SFA.SCA_REF
        AND EXT.OWNER_ENTITY  =  SFA.owner_entity
        AND EXT.EXTRNL_SYS_ID  = '39'
        AND EXT.BP_ID = SFA.BP_ID
        AND EXT.LVL = 5
        AND E1.ELG_SEC_ID            = D1.D1_SEC_ID
        AND D1.D1_ENTITY_ID        = E1.ELG_ENTITY_ID
        AND D1.D1_EVNT_ID          = E1.ELG_EVNT_ID
        AND D1.D1_D1_ID           = E1.ELG_D1_ID
        AND E1.ELG_ENTITY_ID         = SFA.OWNER_ENTITY
        AND E1.ELG_ACNT_ID           = SFA.SCA_REF
        AND i1.owner_entity        = SFA.OWNER_ENTITY
        AND i1.owner_entity        = B1.OWNER_ENTITY
        AND i1.SCA_REF  = SFA.SCA_REF
        AND i1.stat              <> 2
        AND EVT.EVT_ENTITY_ID      ='G'
        AND EVT.EVT_EVNT_ID(+)       = D1.D1_EVNT_ID
        AND EVT.STATUS(+)            = 'AUTHD'
        AND E11.E1_ENTITY_ID       = D1.D1_ENTITY_ID
        AND E11.E1_EVNT_ID         = D1.D1_EVNT_ID
        AND E11.E1_D1_ID          = D1.D1_D1_ID
        AND D1.D1_EVNT_GRP         = 'MEETING'
        AND E1.ELG_FNL_ELGBL_QNTTY   > 0
        AND D1.D1_ENTITY_ID        = 'GSSIN' -- P_D1_ENTITY_ID
        AND B11.ext_db_id LIKE 'LICMF' -- P_GLOBAL_CUSTODIAN
         AND B1.ext_db_id LIKE '%' -- P_FUND_MANAGER
         AND SUBSTR(EXT.LEVEL_EXTNL_SYS,1,5) LIKE '%' -- P_SUB_ACCOUNT_ID
         AND SUBSTR(EXT.LEVEL_EXTNL_SYS,6,9) LIKE '%' -- P_SCHEMA_ID
         AND SUBSTR( D1.D1_EVNT_TYP, 1, 4 ) LIKE '%' -- P_EVENT_TYPE
        AND E11.E1_DT_TYP = 'MEET'
        and D1.D1_evnt_id='12457886544'
       AND D1.OU_ID  IN('17','80') -- ('S1 INDIA PC')
       ) ;

问题根源 #

1. 外连接条件不完整，触发隐式内连接 #

Oracle老式外连接语法（(+)）有规则：如果对某张表使用外连接标记，但该表的部分过滤条件未加(+)，整个外连接会自动转为内连接。

原语句中，EVT.EVT_EVNT_ID(+) = D1.D1_EVNT_ID和EVT.STATUS(+) = 'AUTHD'是外连接条件，但EVT.EVT_ENTITY_ID ='G'未加(+)。这导致Oracle强制要求EVT表必须存在同时满足以下条件的记录：

• EVT_EVNT_ID = D1.D1_EVNT_ID
• STATUS = 'AUTHD'
• EVT_ENTITY_ID ='G'

否则会过滤掉D1表中对应的行。

2. 数据匹配缺失 #

虽然D1.D1_evnt_id='12457886544'存在，但EVT表中大概率没有符合上述三个条件的记录。此时：

• 注释EVT的连接条件后，EVT表不再参与查询，D1等表的数据自然能返回；
• 给EVT.EVT_ENTITY_ID ='G'加(+)后，该条件变为外连接条件，允许EVT表无匹配记录，D1的行就会被保留。

解决办法 #

根据实际业务需求选择：

• 允许无EVT匹配记录时返回数据：把所有EVT相关的过滤条件都加上外连接标记：

  AND EVT.EVT_ENTITY_ID(+) ='G'
  AND EVT.EVT_EVNT_ID(+) = D1.D1_EVNT_ID
  AND EVT.STATUS(+) = 'AUTHD'
  

• 必须要有EVT匹配记录：检查EVT表中是否存在EVT_EVNT_ID='12457886544'且EVT_ENTITY_ID='G'、STATUS='AUTHD'的记录，或者调整过滤条件。

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