15

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!

Improve this question

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for your reply. does it mean exists would always return true/false? IN returns actual values? in my case i should not use EXISTS right? Thanks!
EXISTS returns true/false, as does IN, the main difference between the two is the way that the query optimizer structures the query plan.
3

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');
Improve this answer

1 Comment

" list of matching values. EXISTS returns the boolean values like true or false. " Please prove this assertion with some benchmarks. Because as a bald statement it is false. exists is more efficient for certain types of queries, in is more efficient in other cases.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.