用in函数嵌入子查询作为条件时查出结果为空
问题：
SELECT * FROM SGGCDB_VIEW sv WHERE RES_ID IN (
SELECT urrv.RES_ID FROM IBPS_ERP.USER_ROLE_RES_VIEW urrv WHERE urrv.ID_ = '1069978138403930112'
)
结果未空值。
原因：
- 首先，
SELECT urrv.RES_ID FROM IBPS_ERP.USER_ROLE_RES_VIEW urrv WHERE urrv.ID_ = '1069978138403930112'查询结果为
- 再来考虑，
expression [NOT] IN (subquery)subquery- 子查询返回一列的结果集以测试匹配。该列还必须具有与表达式相同的数据类型。 - 还有，
–只要子查询中返回值包含null值，不要使用not in，因为not in 等同与<> ALL（不等于所有值），就包括<>null ，然后不管是=null还是<>null 都是否，因为判断为空用is null，is not null
比如，a not in(10,null)相当于a!=10 and a!=null,然而a!=null永远为假
所以正确的方法，去掉子查询空值
SELECT urrv.RES_ID FROM IBPS_ERP.USER_ROLE_RES_VIEW urrv WHERE urrv.RES_ID IS NOT NULL
- 那问题还是查询结果为空
那是因为，子查询作为in的条件时是以整列作为条件，意思就是
SELECT a.name FROM a ;
结果是
| name |
|---|
| 张三 |
| 李四 |
| 王五 |
执行语句
SELECT b.* FROM b WHERE b.name IN ( SELECT a.name FROM a ) ;
等同于
SELECT b.* FROM b WHERE b.name IN ( '张三','李四','王五' ) ;
但是如果，执行语句
SELECT c.name FROM c ;
结果为
| name |
|---|
| 张三,李四,王五 |
| 赵六 |
执行语句
SELECT b.* FROM b WHERE b.name IN ( SELECT c.name FROM c ) ;
等同于
SELECT b.* FROM b WHERE b.name IN ( '张三,李四,王五','赵六' ) ;
所以结果永远不可能匹配，因为没有一个b.name=‘张三,李四,王五’ ，因为没有人的名字叫【张三,李四,王五】，最多匹配到b.name=‘赵六’，因为数据中真的有人名字叫做【赵六】的
- 处理方法：
SELECT REGEXP_SUBSTR( xx.RES_ID ,'[^,]+', 1, level )
FROM ( SELECT * FROM (SELECT urrv.RES_ID FROM IBPS_ERP.USER_ROLE_RES_VIEW urrv WHERE urrv.RES_ID IS NOT NULL AND urrv.ID_ = '1069978138403930112' ) WHERE rowNum < 2 ) xx
CONNECT BY REGEXP_SUBSTR(xx.RES_ID ,'[^,]+', 1, level ) IS NOT NULL ;
结果
完整：
SELECT * FROM SGGCDB_VIEW sv WHERE RES_ID IN (
SELECT REGEXP_SUBSTR( xx.RES_ID ,'[^,]+', 1, level )
FROM ( SELECT * FROM (SELECT urrv.RES_ID FROM IBPS_ERP.USER_ROLE_RES_VIEW urrv WHERE urrv.RES_ID IS NOT NULL AND urrv.ID_ = '1069978138403930112' ) WHERE rowNum < 2 ) xx
CONNECT BY REGEXP_SUBSTR(xx.RES_ID ,'[^,]+', 1, level ) IS NOT NULL
);
