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 则没有这种情况。