MySQL中，not in子查询

来源：互联网发布：java项目打jar 编辑：程序博客网时间：2024/06/10 02:26

今天写sql语句的时候，希望通过not in子查询来限制查询结果，实际SQL语句如下：

select ID as id, TYPE_CODE as typeCode , TYPE_NAME as typeName ,

PARENT_ID as parentsId , STYLE as style , LEVELS as levels

from type_code

where PARENT_ID = '30119a0e-2f57-473d-9f1d-2843561e9064' and ID not in

( select PARENT_ID from type_code where PARENT_ID);

结果满足查询的条件为空……

后来发现，子查询中存在字段的某些值为null，所以导致了count=0.

所以，将SQL调整为如下：

select ID as id, TYPE_CODE as typeCode , TYPE_NAME as typeName ,

PARENT_ID as parentsId , STYLE as style , LEVELS as levels

from type_code

where PARENT_ID = '30119a0e-2f57-473d-9f1d-2843561e9064' and ID not in

( select PARENT_ID from type_code where PARENT_ID is not null);

这样就能正确的查出结果了！

总结：MySQL中如果要用not in在子查询中限制结果，那必须要将子查询的结果集限制为不含null，不然查询结果count = 0.

Allen

2011-03-29