The IN and EXISTS are logically the same. The IN clause compares values returned by the subquery and filters out rows in the outer query; the EXISTS clause compares values and filters out rows inside the subquery. In the case of NULL values, the resulting set of rows is the same.
select ename from emp where empno in (select mgr from emp);
select ename from emp e where exists (select 0 from emp where mgr = e.empno);
But problems arise when the logic is reversed to use NOT IN and NOT EXISTS, which return different sets of rows (the first query returns 0 rows; the second returns the intended data--they aren't the same query):
select ename from emp where empno not in (select mgr from emp);
select ename from emp e where not exists (select 0 from emp where mgr =
e.empno);
You can still use the NOT IN query from before, as long as you prevent NULL from being returned in the results (again, these both work, but I'm assuming empno is not null, which is a good assumption in this case):
select ename from emp where empno not in (select mgr from emp where mgr is not
null);
select ename from emp where empno not in (select nvl(mgr,0) from emp);
Comments