在Oracle中用WHERE子句模拟左连接的技术问询

背景与测试数据 #

假设存在两张简单表：

create table t1 (id number(2), name varchar2(12), primary key(id));
create table t2 (t2_id number(2), t1_id number(2), primary key(t2_id));

插入如下测试数据：

insert into t1 values (1,'test');
insert into t1 values (2,'test2');
insert into t2 values (1, 1);
insert into t2 values (3,null);

需求与现有问题 #

目标是模拟左连接效果，对应标准查询语句：

select * from t1 left join t2 on t1.id=t2.t1_id;

自行编写的查询语句如下：

select *
from t1, t2
where t1.id=t2.t1_id or
(t2.t1_id is null and id not in (select t1_id from t2 where t1_id is not null));

该查询可运行，但因交叉连接特性引入了t1的其他关联值，现在需要实现：当第二个条件满足时，t1的所有属性在结果集中显示为NULL。

可行实现方案 #

可以通过以下两种方式实现需求：

方案1：拆分逻辑后用UNION ALL合并 #

将匹配和非匹配的场景分开处理，确保非匹配场景下t1字段置空：

-- 场景1：t1与t2匹配的记录
select t1.id, t1.name, t2.t2_id, t2.t1_id
from t1, t2
where t1.id = t2.t1_id

union all

-- 场景2：t2中t1_id为NULL，且对应t1记录不在t2的非NULL t1_id集合中，t1字段置空
select null as id, null as name, t2.t2_id, t2.t1_id
from t2
where t2.t1_id is null 
  and not exists (select 1 from t1 where t1.id in (select t1_id from t2 where t1_id is not null));

方案2：用CASE表达式动态控制字段值 #

在SELECT列表中通过CASE判断条件，直接设置t1字段的取值：

select 
    case when t2.t1_id is not null then t1.id else null end as id,
    case when t2.t1_id is not null then t1.name else null end as name,
    t2.t2_id,
    t2.t1_id
from t1, t2
where t1.id = t2.t1_id 
   or (t2.t1_id is null and t1.id not in (select t1_id from t2 where t1_id is not null));

以上两种方案都能确保当第二个条件满足时，t1的所有属性显示为NULL，同时避免交叉连接带来的多余关联问题。

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