为何添加过滤条件后，左连接派生表的结果集发生变化？

问题描述 #

两个仅过滤条件不同的左连接查询，结果存在明显差异：

带过滤条件的查询（含NULL行） #

该查询在左右表子查询中均添加了entity = '1'过滤，左表额外限制funding_type in ('G1','G15')，返回结果包含左表无右表匹配的NULL行：

select
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
from
(
select
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from table_a a1, table_b ha
where a1.account = ha.account
and a1.b = '2025'
and a1.a = 'F'
and a1.entity = '1'
and a1.funding_type in ('G1','G15')
group by a1.entity, a1.funding_type, a1.account,ha.parent
) 
z
left join
(
select
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from table_a a, table_b ha
where a.account = ha.account
and a.b = '2025'
and a.a = 'E'
and a.entity = '1'
group by a.entity,ha.parent
) x
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
;

查询结果：

|1|G1|911990|5574605|1|E11|F11|5640568
|1|G1|912990|2777174|1|E12|F12|2810041
|1|G15|900990|98830  | null  |null|F00| null

无过滤条件的查询（无NULL行） #

移除所有entity和funding_type过滤条件后，左表所有行均匹配到右表数据，无NULL值：

select
z.fnd_entity, z.fnd_fund, z.fnd_account,z.fnd_amount, x.exp_entity, x.exp_parent,z.fnd_parent, x.exp_amount
from
(
select
a1.entity fnd_entity, a1.funding_type fnd_fund,ha.parent fnd_parent,a1.account fnd_account,sum(a1.amount) fnd_amount
from table_a a1, table_b ha
where a1.account = ha.account
and a1.b = '2025'
and a1.a = 'F'
group by a1.entity, a1.funding_type, a1.account,ha.parent
) 
z
left join
(
select
a.entity exp_entity, ha.parent exp_parent, sum(a.amount) exp_amount
from table_a a, table_b ha
where a.account = ha.account
and a.b = '2025'
and a.a = 'E'
group by a.entity,ha.parent
) x
on (substr(x.exp_parent,2,2) = substr(z.fnd_account,2,2) and x.exp_entity = z.fnd_entity)
order by x.exp_entity, z.fnd_fund, x.exp_parent,z.fnd_parent
;

查询结果：

|1|G1|911990|5574605    |1  | E11 | F11 | 5640568
|1|G1|912990|2777174    |1  | E12 | F12 | 2810041
|12|G12|900990|2127     |1  | E00 | F00 | 2127
|12|G12|911990|14352    |12 | E11 | F11 | 14352
|12|G12|930990|1971     |12 | E30 | F30 | 1971

核心原因 #

差异本质是右表数据范围变化导致的匹配覆盖度不同：

1. 带过滤条件场景
   右表子查询被限制为entity=1的E类型数据，左表中entity=1的G15行（account=900990）对应的substr(z.fnd_account,2,2)为00，但右表中没有exp_parent后两位为00的entity=1数据，无法满足连接条件，因此左连接保留该行，右表字段显示NULL。

2. 无过滤条件场景
   右表包含了所有entity的E类型数据，覆盖了左表所有行的匹配需求：

   • 对于entity=1的行，仍能匹配到原有的E类型数据；
   • 对于entity=12的行，右表中存在对应entity=12且exp_parent后两位匹配的E类型数据（结果第四、五行可验证）；
   • 结果第三行的x.exp_entity=1应为输入笔误，实际满足x.exp_entity=12的匹配条件，否则该行应显示NULL。

左连接的逻辑是保留左表所有行，右表无匹配则补NULL，无过滤条件时右表数据范围足够覆盖左表所有匹配需求，因此没有NULL行；带过滤条件时右表被限制，无法覆盖部分左表行的匹配，从而出现NULL行。

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