将含EXISTS/NOT EXISTS的SQL查询转为三表关联时结果异常求助

表结构 #

Table_A #

Table_B #

Table_C #

需求说明 #

保留TableA中满足以下全部条件的记录：

• phone_number与account_name同时匹配TableB的某一条记录，或同时匹配TableC的某一条记录
• ID不存在于TableB且不存在于TableC中

原查询问题 #

原查询仅分别校验phone_number和account_name是否存在于B/C表，但未校验两者是否来自同一张表的同一条记录。例如TableA某条记录的phone_number在TableB存在、account_name在TableC存在时，原查询会错误保留这条记录。

原查询语句：

select /*+PARALLEL(su,8)*/ 
       su.ID 
from TableA su 
where (   EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/
                         PHONE_NUMBER 
                  from TableB sa
                  where PHONE_NUMBER=su.PHONE_NUMBER
                 )
       or EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/
                         PHONE_NUMBER
                  from TableC sa
                  where PHONE_NUMBER=su.PHONE_NUMBER
                 )
      )
  and (   EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/
                         ACCOUNT_NAME
                  from TableB sa
                  where ACCOUNT_NAME=su.ACCOUNT_NAME
                 )
       or EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/
                         ACCOUNT_NAME
                  from TableC sa 
                  where ACCOUNT_NAME=su.ACCOUNT_NAME
                 )
      )
and NOT EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/ 
                       ID 
                from TableC sa
                where ID=su.ID
               )
and NOT EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/ 
                       ID
                from TableB sa
                where ID=su.ID
               );

改写查询的问题 #

改写后的三表关联查询存在两个核心问题：

1. 笛卡尔积导致重复结果：直接关联TableB和TableC会生成大量无效的组合行，即使加DISTINCT也无法修正逻辑错误。
2. 逻辑判断失效：当前条件仍允许phone_number匹配TableB、account_name匹配TableC的情况，同时多表关联会引入更多错误匹配，导致结果不符合需求。

改写后的查询语句：

select su.id
from TableA su,
     TableB sa,
     TableC sb
where (   su.PHONE_NUMBER=sa.PHONE_NUMBER
       OR su.PHONE_NUMBER=sb.PHONE_NUMBER
      ) 
  and (su.ACCOUNT_NAME=sa.ACCOUNT_NAME
       OR su.ACCOUNT_NAME=sb.ACCOUNT_NAME
      ) 
   and NOT EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/
                          id 
                   from TableC sa 
                   where id=su.id
                  )
   and NOT EXISTS (select /*+ PARALLEL(sa,8) PARALLEL(su,8)*/
                          id 
                   from TableB sa 
                   where id=su.id
                  )

正确解决方案 #

方案一：精准EXISTS子查询 #

通过EXISTS分别校验TableA记录是否在TableB或TableC中存在同时匹配phone_number和account_name的记录，逻辑清晰且性能可控：

select /*+PARALLEL(su,8)*/ 
       su.ID 
from TableA su 
where (
    -- 匹配TableB同一条记录的手机号和账号名
    EXISTS (
        select 1 
        from TableB sa
        where sa.PHONE_NUMBER = su.PHONE_NUMBER
          and sa.ACCOUNT_NAME = su.ACCOUNT_NAME
    )
    -- 或者匹配TableC同一条记录的手机号和账号名
    OR EXISTS (
        select 1 
        from TableC sa
        where sa.PHONE_NUMBER = su.PHONE_NUMBER
          and sa.ACCOUNT_NAME = su.ACCOUNT_NAME
    )
)
-- 排除ID在B/C表中的记录
and NOT EXISTS (
    select 1 
    from TableB sa
    where sa.ID = su.ID
)
and NOT EXISTS (
    select 1 
    from TableC sa
    where sa.ID = su.ID
);

方案二：UNION ALL合并B/C表后匹配 #

将TableB和TableC的手机号+账号名组合合并后统一匹配，减少子查询数量：

select /*+PARALLEL(su,8)*/ 
       su.ID 
from TableA su
where EXISTS (
    select 1
    from (
        select PHONE_NUMBER, ACCOUNT_NAME from TableB
        union all
        select PHONE_NUMBER, ACCOUNT_NAME from TableC
    ) bc
    where bc.PHONE_NUMBER = su.PHONE_NUMBER
      and bc.ACCOUNT_NAME = su.ACCOUNT_NAME
)
and NOT EXISTS (
    select 1 
    from TableB sa
    where sa.ID = su.ID
)
and NOT EXISTS (
    select 1 
    from TableC sa
    where sa.ID = su.ID
);

性能优化建议 #

• 为TableB和TableC创建(PHONE_NUMBER, ACCOUNT_NAME)联合索引，大幅提升匹配查询的效率。
• 根据数据库实际资源调整并行度参数（PARALLEL(...)中的数字），避免资源过载。
• 优先使用EXISTS子查询而非多表关联，减少无效笛卡尔积的生成。

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