Oracle中多表连接查询优化：替换重复内连接为外连接后结果不符问题求助

首先得帮你理清原查询的核心逻辑，这是你改写出错的关键：
原查询其实是在找同属cfgnum=9999的三条不同X记录的组合：

• 第一条X记录的link关联到A的id
• 第二条X记录的link关联到B的id2，且B的id等于第一条X关联的A的id
• 第三条X记录的link关联到C的id3，且C的nm等于第二条X关联的B的nm
  最后统计所有符合条件的组合总数。

而你尝试的改写逻辑是用单条X记录同时关联A、B、C，这和原查询的逻辑完全不匹配——原查询是三条X记录的组合，你改成了单条X记录要同时满足三个关联条件，结果自然差很多。

优化后的正确写法 #

如果想减少X表的重复扫描次数，可以用CTE（公共表表达式）先提前过滤出cfgnum=9999的X记录，再基于这个小数据集做连接，这样比原查询三次扫描全表X要高效：

WITH cfg_9999_links AS (
    SELECT cfgnum, link
    FROM X
    WHERE cfgnum = 9999
)
SELECT COUNT(*)
FROM A
INNER JOIN cfg_9999_links cl ON A.id = cl.link
INNER JOIN cfg_9999_links cl1 ON cl.cfgnum = cl1.cfgnum
INNER JOIN B ON cl1.link = B.id2 AND B.id = A.id
INNER JOIN cfg_9999_links cl2 ON cl.cfgnum = cl2.cfgnum
INNER JOIN C ON cl2.link = C.id3 AND C.nm = B.nm;

如果你的目标是统计符合条件的A-B-C唯一组合数（而不是原查询中因多条X记录匹配产生的笛卡尔积行数），可以用EXISTS进一步优化，避免不必要的笛卡尔积：

SELECT COUNT(*)
FROM A
INNER JOIN B ON B.id = A.id
INNER JOIN C ON C.nm = B.nm
WHERE EXISTS (
    SELECT 1 FROM X WHERE cfgnum = 9999 AND link = A.id
)
AND EXISTS (
    SELECT 1 FROM X WHERE cfgnum = 9999 AND link = B.id2
)
AND EXISTS (
    SELECT 1 FROM X WHERE cfgnum = 9999 AND link = C.id3
);

用你提供的示例数据测试的话，原查询会返回0（因为没有A的id等于B的id的记录），上面两种优化写法也会返回0，和原查询结果一致。

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