Gold Gold - 9 months ago 33
SQL Question

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

What's the difference between

not in
not exists
in an Oracle query?

When do I use
not in
? And
not exist


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.