小问题,大苦恼(not in 与 not exists)

[yanleigis]

两个表toparcel,tcgrade,内容如下
SQL> select GradeLevel from tcgrade;

GRAD
----
1
2
3
4
5
SQL> select gradelevel,count(*) from toparcel
2 where substr(LandUsecode,1,2)='11' group by gradelevel;

GRAD COUNT(*)
---- ----------
1 166
2 126
3 103
4 231
5 211
14

使用not in
SQL> select count(*) from toparcel a
2 where substr(LandUsecode,1,2)='11' and
3 (a.gradelevel not in (select GradeLevel from tcgrade));

COUNT(*)
----------
0
显然不对
SQL> select count(*) from toparcel a
2 where substr(LandUsecode,1,2)='11' and
3 not (a.gradelevel in (select GradeLevel from tcgrade));

COUNT(*)
----------
0
也不对
而这样是对
SQL> select count(*) from toparcel a
2 where substr(LandUsecode,1,2)='11' and not exists
3 (select GradeLevel from tcgrade b where b.gradelevel=a.gradelevel);

COUNT(*)
----------
14
我想知道,not in怎么写是正确的

[beckhambobo]

这样不行吗？
select count(*) from toparcel a,tcgrade b
where substr(LandUsecode,1,2)='11' and a.gradelevel<>b.gradelevel;

[maohaisheng]

你的观察真是细致
NOT IN Equivalent to "!=ALL".
Evaluates to FALSE if any member of the set is NULL. 如果集合成员为空的话，返回false.

GRAD COUNT(*)
---- ----------
1 166
2 126
3 103
4 231
5 211
14

14个成员为空，运算返回false,所以没有查出来。

还有，not in 是不提倡使用的SQL之一，上数据库课的时候老师说，所有not in的操作可以转化为什么什么，我忘了

[maohaisheng]

我一般用外联结加判断空值来判断两个集合的not in关系

[tuidler]

SQL> select count(*) from toparcel a
2 where substr(LandUsecode,1,2)='11' and
3 (a.gradelevel not in (select GradeLevel from tcgrade));

这个NOT IN的写法应该是对的，但是空的记录是不能进行比较的，你可以将它转换一下
SQL> select count(*) from toparcel a
2 where substr(LandUsecode,1,2)='11' and
3 (nvl(a.gradelevel,0) not in (select GradeLevel from tcgrade));

[tuidler]

或者这么写也可以（就针对你的例子而言）
SQL> select count(*) from toparcel a ,tcgrad b
2 where substr(LandUsecode,1,2)='11' and
3 a.gradlevel=b.gradelevel(+) and a.gradlevel is null