一般在SELECT外面加层括号可以把集合变成值,空集变成NULL。但对NOT IN不起作用。如果NOT IN LIST后面多于一项则起作用,空集就变成空值了。
=========================================
NOT IN LIST后面只有一项的时候:
SELECT *
FROM DUAL
WHERE 1 NOT IN (SELECT NULL FROM DUAL WHERE 1=2);
D
-
X
FROM DUAL
WHERE 1 NOT IN (SELECT NULL FROM DUAL WHERE 1=2);
D
-
X
这里子查询返回的是空集,没有将空集变成NULL。
----------------------------------------------------------
NOT IN LIST后面多于一项的时候:
SELECT *
FROM DUAL
WHERE 1 NOT IN ((SELECT NULL FROM DUAL WHERE 1=2), 2);
FROM DUAL
WHERE 1 NOT IN ((SELECT NULL FROM DUAL WHERE 1=2), 2);
no rows selected
这里Oracle将(SELECT NULL FROM DUAL WHERE 1=2)返回的
空集转换成空值NULL。
于是,where条件等价于 1 <> (NULL, 2)
----------------------------------------------------------
IN的情况:
SELECT *
FROM DUAL
WHERE 1 IN ((SELECT NULL FROM DUAL WHERE 1=2),1);
FROM DUAL
WHERE 1 IN ((SELECT NULL FROM DUAL WHERE 1=2),1);
D
-
X
-
X
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28215864/viewspace-755778/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28215864/viewspace-755778/