为何Left Join查询行数多于左表？PL/SQL技术求助

问题现象 #

执行PL/SQL查询时出现行数不符合预期的异常：

• agentColAlok1返回833条记录，agentColAlok2返回911条记录，行数莫名增加
• 基于agentColAlok2的agentColTp返回11090条记录，远多于源表的911条
• 替换为Inner Join后问题仍未解决

异常查询代码 #

WITH
    subsi AS (
        SELECT
            mrk2.CUSTOMER_NUMBER ,
            mrk2.CUSTOMER_NAME ,
            mrk2.GROUP_NAME 
        FROM MOJU_REV_KLASIFIKASI mrk2
        WHERE mrk2.GROUP_NAME LIKE '%' || 'Anak perusahaan' || '%'
            OR mrk2.GROUP_NAME LIKE '%' || 'Anak Perusahaan dari Entitas Asosiasi Telkom Group' || '%'
            OR mrk2.GROUP_NAME LIKE '%' || 'Anak Perusahaan' || '%'
    ),
    agentColRef1 AS (
        SELECT 
            mrka.CONTRACT_NUMBER ,
            mrka.BP_NUMBER ,
            mrka.CUSTOMER_NAME ,
            mrka.GROUP_NAME ,
            mrka.UBIS ,
            mrka.REFR ,
            mrka.GL_ACC ,
            mrka.TOT_COST ,
            mak.KL_REF1 AS NAMA_MITRA,
            mak.KL_REF2 AS KL_NUMBER    
        FROM
            MOJU_REV_KK43_ADJ mrka
        LEFT JOIN MOJU_AGENT_KK11 mak ON mrka.CONTRACT_NUMBER = mak.CONTRACT_NUMBER 
        WHERE mrka.tahun = 2022 AND mrka.q = 6 AND mrka.TOT_COST != 0 AND mrka.REFR = '1.1'
        GROUP BY mrka.CONTRACT_NUMBER , mrka.BP_NUMBER , mrka.CUSTOMER_NAME, mrka.GROUP_NAME , mrka.ubis, mrka.refr,
        mrka.GL_ACC , mrka.TOT_COST, mak.KL_REF1 , mak.KL_REF2 
    ),
    agentColRef2 AS (
        SELECT 
            mrka.CONTRACT_NUMBER ,
            mrka.BP_NUMBER ,
            mrka.CUSTOMER_NAME ,
            mrka.GROUP_NAME ,
            mrka.UBIS ,
            mrka.REFR ,
            mrka.GL_ACC ,
            mrka.TOT_COST ,
            mak.KL_REF1 AS NAMA_MITRA,
            mak.KL_REF2 AS KL_NUMBER    
        FROM
            MOJU_REV_KK43_ADJ mrka
        LEFT JOIN MOJU_AGENT_KK12 mak ON mrka.CONTRACT_NUMBER = mak.CONTRACT_NUMBER 
        WHERE mrka.tahun = 2022 AND mrka.q = 6 AND mrka.TOT_COST != 0 AND mrka.REFR = '1.2'
        GROUP BY mrka.CONTRACT_NUMBER , mrka.BP_NUMBER , mrka.CUSTOMER_NAME, mrka.GROUP_NAME , mrka.ubis, mrka.refr,
        mrka.GL_ACC , mrka.TOT_COST, mak.KL_REF1 , mak.KL_REF2 
    ),
    agentColRef3 AS (
        SELECT 
            mrka.CONTRACT_NUMBER ,
            mrka.BP_NUMBER ,
            mrka.CUSTOMER_NAME ,
            mrka.GROUP_NAME ,
            mrka.UBIS ,
            mrka.REFR ,
            mrka.GL_ACC ,
            mrka.TOT_COST ,
            mak.KL_REF1 AS NAMA_MITRA,
            mak.KL_REF2 AS KL_NUMBER    
        FROM
            MOJU_REV_KK43_ADJ mrka
        LEFT JOIN MOJU_AGENT_KK13 mak ON mrka.CONTRACT_NUMBER = mak.CONTRACT_NUMBER 
        WHERE mrka.tahun = 2022 AND mrka.q = 6 AND mrka.TOT_COST != 0 AND mrka.REFR = '1.3'
        GROUP BY mrka.CONTRACT_NUMBER , mrka.BP_NUMBER , mrka.CUSTOMER_NAME, mrka.GROUP_NAME , mrka.ubis, mrka.refr,
        mrka.GL_ACC , mrka.TOT_COST, mak.KL_REF1 , mak.KL_REF2 
    ),
    agentColRefUnion AS (
        SELECT *
        FROM agentColRef1
        UNION
        SELECT *
        FROM agentColRef2
        UNION
        SELECT *
        FROM agentColRef3
    ),
    agentColAlok1 AS ( 
        SELECT 
            ac.CONTRACT_NUMBER AS CONTRACT_NUMBER ,
            ac.BP_NUMBER AS BP_NUMBER ,
            ac.CUSTOMER_NAME AS CUSTOMER_NAME ,
            ac.GROUP_NAME AS GROUP_NAME ,
            ac.ubis,
            ac.REFR ,
            ac.GL_ACC ,
            ac.TOT_COST ,
            ac.NAMA_MITRA,
            ac.KL_NUMBER,
            mabc.GL AS AKUN_BEBAN_1
        FROM 
            agentColRefUnion ac
        LEFT OUTER JOIN MOJU_AGENT_BEBAN_CPE mabc ON ac.KL_NUMBER = mabc.KL
        GROUP BY ac.CONTRACT_NUMBER , ac.BP_NUMBER , ac.CUSTOMER_NAME, ac.GROUP_NAME , ac.ubis, ac.refr,
        ac.GL_ACC , ac.TOT_COST, ac.nama_mitra , ac.kl_number, mabc.GL
    ),
    agentColAlok2 AS ( 
        SELECT 
            ac.CONTRACT_NUMBER,
            ac.BP_NUMBER ,
            ac.CUSTOMER_NAME ,
            ac.GROUP_NAME ,
            ac.ubis,
            ac.REFR ,
            ac.GL_ACC ,
            ac.TOT_COST ,
            ac.NAMA_MITRA,
            ac.KL_NUMBER,
            ac.AKUN_BEBAN_1,
            CASE
                WHEN (ac.AKUN_BEBAN_1 IS NULL) THEN maba.BEBAN_DNAPSO ELSE to_char(ac.AKUN_BEBAN_1)
            END AS AKUN_BEBAN_2
        FROM 
            agentColAlok1 ac
        LEFT JOIN MOJU_AGENT_BEBAN_AKUN maba ON ac.GL_ACC = maba.GL_REVENUE 
    ),
    agentColTp AS (
        SELECT 
            ac.CONTRACT_NUMBER,
            ac.BP_NUMBER ,
            ac.CUSTOMER_NAME ,
            ac.GROUP_NAME ,
            ac.ubis,
            ac.REFR ,
            ac.GL_ACC ,
            ac.TOT_COST ,
            ac.NAMA_MITRA,
            ac.KL_NUMBER,
            ac.AKUN_BEBAN_1,
            ac.AKUN_BEBAN_2,
            mrl.CUSTOMER_TP 
        FROM 
            agentColAlok2 ac
        LEFT JOIN MOJU_REV_LTP mrl ON ac.NAMA_MITRA = mrl.SUBSIDIARIES 
    )

SELECT
    COUNT(*)
FROM agentColAlok2 aca
WHERE aca.nama_mitra IS NOT NULL AND aca.nama_mitra != 'N/A'

问题原因分析 #

1. agentColAlok1到agentColAlok2行数增加 #

agentColAlok2通过ac.GL_ACC = maba.GL_REVENUE左连接MOJU_AGENT_BEBAN_AKUN表。如果该表中同一个GL_REVENUE对应多条记录，左连接会将agentColAlok1的单条记录与所有匹配项关联，导致行数增加。比如原表1条记录匹配2条关联表记录，最终会变成2条。

2. agentColTp行数暴增的核心原因 #

agentColTp通过ac.NAMA_MITRA = mrl.SUBSIDIARIES左连接MOJU_REV_LTP表。如果该表中同一个SUBSIDIARIES存在多条记录，每条agentColAlok2的记录会匹配所有符合条件的关联表记录，造成行数爆炸。按911条源记录计算，若平均每条匹配12条关联记录，就会得到911*12≈11090条结果。

3. 无意义GROUP BY的误导 #

agentColRef1/2/3和agentColAlok1中使用了GROUP BY，但没有搭配聚合函数（如SUM、COUNT），仅起到去重作用。但这种去重无法阻止后续JOIN操作因关联表重复数据带来的行数膨胀，反而容易让开发者忽略关联表的数据重复问题。

解决建议 #

1. 排查关联表的重复数据 #

执行以下查询确认重复数据：

-- 检查MOJU_AGENT_BEBAN_AKUN中重复的GL_REVENUE
SELECT GL_REVENUE, COUNT(*) 
FROM MOJU_AGENT_BEBAN_AKUN 
GROUP BY GL_REVENUE 
HAVING COUNT(*) > 1;

-- 检查MOJU_REV_LTP中重复的SUBSIDIARIES
SELECT SUBSIDIARIES, COUNT(*) 
FROM MOJU_REV_LTP 
GROUP BY SUBSIDIARIES 
HAVING COUNT(*) > 1;

2. 清理或去重关联表数据 #

• 如果重复数据是无效的，直接清理冗余记录；
• 如果重复数据是业务允许的，使用窗口函数选择需要保留的记录（如取第一条、最新记录），再进行JOIN：

-- 示例：对MOJU_REV_LTP去重后再关联
WITH mrl_clean AS (
    SELECT SUBSIDIARIES, CUSTOMER_TP,
           ROW_NUMBER() OVER (PARTITION BY SUBSIDIARIES ORDER BY 1) rn
    FROM MOJU_REV_LTP
)
SELECT 
    ac.CONTRACT_NUMBER,
    ac.BP_NUMBER ,
    ac.CUSTOMER_NAME ,
    ac.GROUP_NAME ,
    ac.ubis,
    ac.REFR ,
    ac.GL_ACC ,
    ac.TOT_COST ,
    ac.NAMA_MITRA,
    ac.KL_NUMBER,
    ac.AKUN_BEBAN_1,
    ac.AKUN_BEBAN_2,
    mrl.CUSTOMER_TP 
FROM 
    agentColAlok2 ac
LEFT JOIN mrl_clean mrl ON ac.NAMA_MITRA = mrl.SUBSIDIARIES AND mrl.rn = 1;

3. 替换无意义的GROUP BY #

将agentColRef1/2/3和agentColAlok1中的GROUP BY替换为SELECT DISTINCT，逻辑更清晰；如果原表数据本身无重复，可直接去掉GROUP BY。

内容的提问来源于stack exchange，提问作者Muhammad Dzulfiqar Firdaus