如何在Oracle中通过Join减少查询深度优化flag为1的用户查询？

表结构 #

• t0表：user_id、name字段
• t1表：id、user_id字段
• t2表：id、flag字段
• t3表：id、base_id、flag字段（t2的子表）
• t4表：id、base_id、flag字段（t3的子表）

业务规则 #

若父表数据的flag为1，则子表数据的flag也为1；反之（子表flag为1时）父表flag不一定为1。

查询需求 #

查询所有flag为1的数据对应的用户名称。

现有嵌套子查询SQL #

select t1.id, t0.name from t0 join t1 on t0.user_id = t1.id where t1.id in 
  (select id from t2 where flag = 1 
   union 
   select base_id from t3 where flag = 1 or id in
     (select base_id from t4 where flag = 1)
  )
;

优化方案：使用Join减少查询深度 #

可以通过左连接+去重的方式替代嵌套子查询，利用Oracle优化器对Join的处理逻辑，减少子查询嵌套层级，提升查询效率：

SELECT DISTINCT t1.id, t0.name
FROM t0
JOIN t1 ON t0.user_id = t1.id
-- 关联t2中flag=1的记录
LEFT JOIN t2 ON t1.id = t2.id AND t2.flag = 1
-- 关联t3中flag=1，或其关联的t4中flag=1的记录
LEFT JOIN t3 ON t1.id = t3.base_id 
    AND (t3.flag = 1 OR EXISTS (
        SELECT 1 FROM t4 WHERE t4.base_id = t3.id AND t4.flag = 1
    ))
-- 过滤出至少匹配到t2或t3中符合条件的记录
WHERE t2.id IS NOT NULL OR t3.base_id IS NOT NULL;

优化说明 #

1. 用左连接直接关联各层级表，避免多层嵌套子查询的复杂逻辑，让Oracle优化器更容易生成高效执行计划
2. 通过DISTINCT确保最终结果无重复（同一用户可能同时匹配t2和t3的符合条件记录）
3. 完整保留原逻辑的三类匹配场景：t2的flag=1、t3自身flag=1、t3关联的t4 flag=1

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