无WHERE子句的SQL Left Outer Join未返回左表全部行问题

问题复现 #

测试表结构与数据 #

-- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
drop table t1;
drop table t2;

create table t1 
(ov_product          varchar2(18 byte)
,product             varchar2(18 byte)
)
/

create table t2 
(reporting_month     number
,product             varchar2(18 byte) 
,sender              varchar2(2 byte) 
,items               number 
)
/

insert into t1
(
select 'Product 1' ov_product, 'P1' product from dual
union
select 'Product 2' ov_product, 'P2' product from dual
union
select 'Product 3' ov_product, 'P3' product from dual
union
select 'Product 4' ov_product, 'P4' product from dual
);

insert into t2
(
select 202108, 'P1', 'AT', 100 from dual
union
select 202108, 'P2', 'AT', 25 from dual
union
-- no P3 for AT
select 202108, 'P4', 'AT', 57 from dual
union
select 202108, 'P1', 'GR', 45 from dual
union
select 202108, 'P2', 'GR', 22 from dual
union
select 202108, 'P3', 'GR', 5 from dual
union
select 202108, 'P4', 'GR', 4 from dual
)
;
commit;

原查询SQL #

select t1.ov_product
      ,t2.sender
      ,t2.items
  from t1
  left outer join t2
    on t1.product = t2.product
 order by 2, 1   
;

问题根因 #

• 你预期得到4个产品 × 2个发件方（AT/GR）共8行结果，缺失行为Product 3 + AT的空值配对，该结果不符合预期的核心原因是关联逻辑不匹配业务需求，不是Oracle语法或版本问题。
• 左连接规则仅保证左表（t1）的行不会因为关联不上而丢失，不会自动生成右表（t2）的维度值和左表行的全量配对：t2中P3仅存在sender为GR的记录，不存在sender为AT的记录，因此原查询只会返回P3和GR的关联行，不会生成P3和AT的空值行。
• 原查询返回的7行结果是符合SQL语法逻辑的正常输出。

修正方案 #

先获取所有需要展示的sender唯一值，和t1的所有产品做笛卡尔积得到全量维度组合，再左关联t2的交易数据即可得到预期8行结果，修正后SQL如下：

SELECT 
    t1.ov_product,
    t2_sender.sender,
    t2.items
FROM t1
-- 生成所有产品+所有发件方的全量组合
CROSS JOIN (SELECT DISTINCT sender FROM t2) t2_sender
-- 左关联匹配交易数据
LEFT OUTER JOIN t2
    ON t1.product = t2.product 
    AND t2_sender.sender = t2.sender
ORDER BY 2,1;

如果需要按统计月份过滤，将过滤条件加在获取sender的子查询中即可，例如仅统计202108月份的数据：

CROSS JOIN (SELECT DISTINCT sender FROM t2 WHERE reporting_month = 202108) t2_sender

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