这样不行吗?
select count(*) from toparcel a,tcgrade b
where substr(LandUsecode,1,2)='11' and a.gradelevel<>b.gradelevel;
select count(*) from toparcel a,tcgrade b
where substr(LandUsecode,1,2)='11' and a.gradelevel<>b.gradelevel;
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
1414个成员为空,运算返回false,所以没有查出来。还有,not in 是不提倡使用的SQL之一,上数据库课的时候老师说,所有not in的操作可以转化为什么什么,我忘了
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));
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