What's the difference between 'not in' and 'not exists'?

What's the difference between

not in

and

not exists

in an Oracle query?

When do I use

not in

? And

not exist

shahkalpesh

Answer

I think it serves the same purpose.

not in can also take literal values whereas not exists need a query to compare the results with.

EDIT: not exists could be good to use because it can join with the outer query & can lead to usage of index, if the criteria uses column that is indexed.

EDIT2: See this question as well.

EDIT3: Let me take the above things back.
See this link. I think, it all depends on how the DB translates this & on database/indexes etc.

Source (Stackoverflow)

http://stackoverflow.com/questions/1699424/whats-the-difference-between-not-in-and-not-exists

What's the difference between 'not in' and 'not exists'?

Sign up