SQLSERVER not in 的问题

SELECT FROM TABLE_A  WHERE TABLE_A.ID NOT IN(SELECT FOREN_ID FROM TABLE_B)

SELECT FROM TABLE_A  WHERE TABLE_A.ID  IN(SELECT FOREN_ID FROM TABLE_B)

 not in的时候 如果SELECT FOREN_ID FROM TABLE_B 返回的值有NULL值的话，则整个语句SELECT FROM TABLE_A  WHERE TABLE_A.ID NOT IN(SELECT FOREN_ID FROM TABLE_B)返回的都是空集，查不到任何记录。办法是排除掉NULL值，修改为：

SELECT FROM TABLE_A  WHERE TABLE_A.ID NOT IN(SELECT FOREN_ID FROM TABLE_B WHERE FOREN_ID IS NOT NULL)

 

而in 则没有这种情况。