mysql not in子查询返回结果为空

来源：互联网发布：绝地求生帧数优化软件编辑：程序博客网时间：2024/05/19 04:04

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

--本篇文章转自：http://blog.csdn.net/allenlinrui/article/details/6286954

0 0