Oracle SQL左连接查询结果不符，如何获取预期结果？

排查方向与调整方案 #

1. 处理关联表的一对多数据（避免笛卡尔积） #

如果Table2或Table3中，同一组cust_id + subscr对应多条记录，左连接后会产生笛卡尔积，导致结果行数、数据不符合预期。可以通过聚合或去重先处理关联表：

• 若需取每组的最新/最大ID值，用聚合函数：

select t1.cust_id, t1.subscr, t2.id1, t2.id2, t3.id3
from table1 t1
left join (
    select cust_id, subscr, max(id1) as id1, max(id2) as id2
    from table2
    group by cust_id, subscr
) t2 on t1.cust_id = t2.cust_id and t1.subscr = t2.subscr
left join (
    select cust_id, subscr, max(id3) as id3
    from table3
    group by cust_id, subscr
) t3 on t1.cust_id = t3.cust_id and t1.subscr = t3.subscr

• 若只需每组任意一条匹配记录，用row_number()去重：

select t1.cust_id, t1.subscr, t2.id1, t2.id2, t3.id3
from table1 t1
left join (
    select cust_id, subscr, id1, id2,
           row_number() over(partition by cust_id, subscr order by 1) rn
    from table2
) t2 on t1.cust_id = t2.cust_id and t1.subscr = t2.subscr and t2.rn = 1
left join (
    select cust_id, subscr, id3,
           row_number() over(partition by cust_id, subscr order by 1) rn
    from table3
) t3 on t1.cust_id = t3.cust_id and t1.subscr = t3.subscr and t3.rn = 1

2. 修正关联字段名 #

原需求提到关联字段是sub_id，但SQL中用的是subscr，需确认三张表的关联字段名是否一致：
如果实际关联字段为sub_id，替换所有subscr为sub_id即可：

select t1.cust_id, t1.sub_id, t2.id1, t2.id2, t3.id3
from table1 t1
left join table2 t2 on t1.cust_id = t2.cust_id and t1.sub_id = t2.sub_id
left join table3 t3 on t1.cust_id = t3.cust_id and t1.sub_id = t3.sub_id

3. 规范表别名 #

原SQL中Table3未指定别名，虽然Oracle允许，但易造成逻辑混淆，建议统一添加别名（如t3），提升代码可读性。

4. 处理空值匹配问题 #

Oracle中null = null不成立，若cust_id或subscr存在空值，需显式处理空值匹配：

select t1.cust_id, t1.subscr, t2.id1, t2.id2, t3.id3
from table1 t1
left join table2 t2 on 
    (t1.cust_id = t2.cust_id or (t1.cust_id is null and t2.cust_id is null))
    and (t1.subscr = t2.subscr or (t1.subscr is null and t2.subscr is null))
left join table3 t3 on 
    (t1.cust_id = t3.cust_id or (t1.cust_id is null and t3.cust_id is null))
    and (t1.subscr = t3.subscr or (t1.subscr is null and t3.subscr is null))

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