Oracle SQL使用中间视图与直接查询结果不一致问题咨询

问题场景 #

先看创建表和视图的代码：

create table persons (
  id number not null,
  client_id number not null
    constraint foo_fk
      references clients,

  person_code number -- 允许为空
    constraint foobar_fk
      references codes, -- 存储编码的其他表

  is_active char,

  constraint foo_pk
    primary key (id, client_id)

)
/


create view active_persons_v as
select
    id, client_id, person_code

  from persons where is_active = 'Y'
/


create table clients (
  id number not null
    constraint whatever_pk
    primary key
)
/

执行两个查询后出现结果差异：

1. 使用视图的查询：

select
  clients.id,
  active_persons_v.id,
  active_persons_v.person_code

from clients
left join active_persons_v -- 关联视图
  on clients.id = active_persons_v.client_id
where
  clients.id = 4444;

该查询会返回clients.id=4444的所有行，包括没有匹配活跃person时person_code为NULL的行。

2. 直接关联表的查询：

select
  clients.id,
  persons.id,
  persons.person_code

from clients
left join persons -- 直接关联原表
  on clients.id = persons.client_id
where
  clients.id = 4444
  and persons.is_active = 'Y'; -- 试图模拟视图的过滤逻辑

该查询会丢失person_code为NULL的行，和第一个查询结果不一致。

交换LEFT JOIN的表顺序后，第二个查询结果和第一个一致，这是为什么？

核心原因：过滤条件的位置决定了执行逻辑 #

视图查询的执行逻辑 #

视图active_persons_v的定义是先从persons表中筛选出is_active='Y'的记录，再把这个筛选后的结果集和clients表做LEFT JOIN。LEFT JOIN的逻辑是：无论右表（视图结果集）有没有匹配的记录，左表（clients）的行都会被保留，没有匹配时对应的视图字段为NULL。所以最终会保留clients.id=4444的所有行，包括无匹配活跃person的情况。

直接关联表查询的问题 #

把persons.is_active='Y'放在WHERE子句中，执行逻辑完全不同：

1. 先执行clients和persons的LEFT JOIN，此时没有匹配person的clients行，对应的persons字段（包括is_active）都会是NULL。
2. 再执行WHERE子句过滤，persons.is_active='Y'会把is_active为NULL的行排除——因为NULL和任何值比较的结果都是未知，不符合=的条件，所以这些行被过滤掉，最终丢失了person_code为NULL的行。

正确的等价写法 #

要让直接关联表的查询和视图查询结果一致，必须把is_active='Y'的过滤条件放到LEFT JOIN的ON子句中，这样会先筛选出符合条件的person记录，再进行关联：

select
  clients.id,
  persons.id,
  persons.person_code

from clients
left join persons
  on clients.id = persons.client_id
  and persons.is_active = 'Y' -- 过滤条件移到ON子句
where
  clients.id = 4444;

交换表顺序后结果一致的原因 #

当交换LEFT JOIN的表顺序（比如persons LEFT JOIN clients），此时persons是左表，WHERE子句中的persons.is_active='Y'是对左表的过滤——LEFT JOIN的左表行都会被保留，过滤条件只是筛选出左表中活跃的记录，之后再和clients关联并筛选clients.id=4444的行。这个逻辑和视图查询的逻辑一致：先筛选活跃的person，再关联目标client，所以最终结果和视图查询匹配。

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