Sql奇技淫巧之EXIST实现分层过滤

在这样一个场景，我 left join 了很多张表，用这些表的不同列来过滤，看起来非常合理
但是出现的问题是 left join 其中一张或多张表出现了笛卡尔积，且无法消除

FUNCTION fun_get_xxx_helper(v_param_1 VARCHAR2,v_param_2 VARCHAR2,v_param_3 VARCHAR2) 
RETURN t_xxx_tab pipelined 
ASv_cur t_xxx_cur;v_rec v_cur%ROWTYPE;
BEGINOPEN v_cur FORSELECT R.*FROM (SELECT one.colum_1,one.colum_2,three.colum_1,two.colum_1FROM table_one oneLEFT JOIN table_two two ON one.colum_1= two.colum_1LEFT JOIN table_three three ON one.colum_1= three.colum_1-- left join table_four 是为了用它其中一列作为过滤条件，但是会产生笛卡尔积，且无法消除LEFT JOIN table_four four ON one.colum_1= four.colum_1 WHERE (v_param_1 is not null AND one.colum_1= v_param_1 ) AND(v_param_2 is null or two.colum_2 = v_param_2 ) AND-- 在这里用到了 table_four.colum_3 作为过滤条件(v_param_3 is null or four.colum_3 = v_param_3 ))R-- 输出部分LOOPFETCH v_cur INTO v_rec;EXIT WHEN v_cur%notfound;PIPE ROW (v_rec);END LOOP;
END fun_get_xxx_helper;

这个时候可以把原本会产生笛卡尔积的那张表先舍弃掉，把它放在外层 select 的 where 子句中，以子查询的方式实现过滤

改良后：

FUNCTION fun_get_xxx_helper(v_param_1 VARCHAR2,v_param_2 VARCHAR2,v_param_3 VARCHAR2) 
RETURN t_xxx_tab pipelined 
ASv_cur t_xxx_cur;v_rec v_cur%ROWTYPE;
BEGINOPEN v_cur FORSELECT R.*FROM (SELECT one.colum_1 id,one.colum_2 name,three.colum_1 age,two.colum_1 genderFROM table_one oneLEFT JOIN table_two two ON one.colum_1= two.colum_1LEFT JOIN table_three three ON one.colum_1= three.colum_1WHERE (v_param_1 is not null AND one.colum_1= v_param_1 ) AND(v_param_2 is null or two.colum_2 = v_param_2 ))RWHERE v_param_3 is null OR( EXISTS ( SELECT 1FROM table_four fourWHERE four.colum_1= R.id AND four.colum_3  = v_param_3 ))-- 输出部分LOOPFETCH v_cur INTO v_rec;EXIT WHEN v_cur%notfound;PIPE ROW (v_rec);END LOOP;
END fun_get_xxx_helper;

在里层 select 中先把前面的过滤做了，然后在外层的 select 的 where 子句中过滤
v_param_3 为null则不过滤，不为空则用EXISTS ()函数配合select 1子查询来做过滤
以R.id和v_param_3作为过滤条件查询table_four中是否有此数据，若有则保留里层R.id对应的那条数据，没有则将其过滤掉；

1. select 1表示当含有满足条件的时候返回一个常量 1，可以看作一条 record
2. EXISTS是SQL中的一个逻辑运算符，通常用于检查子查询中的行是否存在；
   EXISTS (subquery)它根据子查询是否返回任何行返回一个布尔值，如果子查询至少返回一行，则返回结果为True，子查询没有返回任何行，则返回结果为False；
   通常与WHERE或HAVING子句配合使用，有条件地过滤或连接数据；
   =================================================================
   例如，在执行删除或更新数据之前，可以使用EXISTS检查相关表中是否存在这条记录：

   DELETE FROM table_name
   WHERE EXISTS (SELECT 1 FROM related_table WHERE related_table.id = table_name.id);