Oracle IN vs Exists difference?

I am confused with oracle IN and EXISTS. I have below requirement.

I need to get all the employees whose names are in-

select * from emp where ename in('smith','brown','john','johnson');

Can i use EXISTS here? Also IN clause has 1000 limitation. Does EXISTS also has any such limitation?

Thanks!

Solution 1:

IN picks the list of matching values. EXISTS returns the boolean values like true or false. Exists is faster than in.

Example

IN

select ename from emp e where mgr in(select empno from emp where ename='KING');

EXISTS

select ename from emp e 
  where exists (select 1 from emp where e.mgr = empno and ename = 'KING'); 

Solution 2:

simply put, EXISTS is usually used for checking whether rows that meet a criteria exist in another (or the same) table.

your SQL using EXISTS would look like this:

select *
from   emp e
where  exists(select * from emp e2 where e.empno = e2.empno and e2.ename in ('smith', 'brown', 'john', 'johnson'))

so you can see it's not what you need here