MySQL中，not in子查询

今天写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