针对一个 NOT IN 语句:
SELECT OBJECT_ID FROM HSP_OBJECT T WHERE T.OBJECT_ID NOT IN ( SELECT DISTINCT FORM_ID FROM HSP_FORM TT ) ORDER BY T.OBJECT_ID ;
如果数据量不大,那么没有问题。
但是数据量过大的时候,NOT IN 效率差的特点会十分明显。
此时就需要使用其他方法:
常见的可以用 外部连接、NOT EXISTS 来替换:
1. 外部连接:
SELECT T1.OBJECT_ID FROM HSP_OBJECT T1 LEFT JOIN HSP_FORM T2 ON T1.OBJECT_ID = T2.FORM_ID WHERE T2.FORM_ID IS NULL ORDER BY T1.OBJECT_ID ;
2. NOT EXISTS:
SELECT OBJECT_ID FROM HSP_OBJECT T WHERE NOT EXISTS ( SELECT 'G' FROM HSP_FORM TT WHERE TT.FORM_ID = T.OBJECT_ID ) ORDER BY T.OBJECT_ID ;
另:IN 和 NOT IN 都建议使用 EXISTS 和 NOT EXISTS 代替。