please don't reference that site.
but - funny enough - if it were entirely correct (it isn't) - then it would be telling you to USE a correlated subquery since most all EXISTS queries are in fact correlated (they are not generally useful otherwise)!!!
NOT IN correlated subquery doesn't sound right. Demonstrate to me an "on purpose not in correlated subquery".
This is a correlated subquery, we reference DEPT.DEPTNO in the subquery - that is the 'correlated' part:
select * from dept where NOT EXISTS (select null from emp where emp.deptno = dept.deptno );
This is a not in subquery - we would NOT generally correlate it - we generate a 'set' - we do not reference DEPT in the subquery. Note: very very very important to have the where deptno is not null - without it, performance will be very very bad AND the answer would be different from above!!!!)
select * from dept where deptno NOT IN (select deptno from emp WHERE DEPTNO IS NOT NULL);
Perhaps you were using non-correlated NOT IN's that the optimizer DID NOT KNOW could not return a null. If they can (NOT IN's only) return NULL - then performance can be pretty bad AND you probably get the wrong answer if there ever is a null.
ops$tkyte%ORA11GR2> connect scott/tiger
Connected.
scott%ORA11GR2> select * from dept where not exists (select null from emp where emp.deptno = dept.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.03
scott%ORA11GR2> select * from dept where deptno NOT IN (select deptno from emp where deptno IS NOT NULL);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott%ORA11GR2> select * from dept where deptno NOT IN (select deptno from emp);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
<b>they look the same, but the ARE NOT</b>
scott%ORA11GR2> update emp set deptno = null where rownum = 1;
1 row updated.
Elapsed: 00:00:00.00
scott%ORA11GR2> select * from dept where deptno NOT IN (select deptno from emp);
no rows selected
Elapsed: 00:00:00.00
<b>no data!!!</b>
scott%ORA11GR2> select * from dept where deptno NOT IN (select deptno from emp where deptno IS NOT NULL);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
scott%ORA11GR2> select * from dept where not exists (select null from emp where emp.deptno = dept.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.00
scott%ORA11GR2>