What's the difference between not in and not exists in an Oracle query?
When do I use not in? And not exist?
Add a comment |
4 Answers
The difference between NOT IN and NOT EXISTS becomes clear where there are NULL values included in the result.
For example:
create table test_a (col1 varchar2(30 char)); create table test_b (col1 varchar2(30 char)); insert into test_a (col1) values ('a'); insert into test_a (col1) values ('b'); insert into test_a (col1) values ('c'); insert into test_a (col1) values ('d'); insert into test_a (col1) values ('e'); insert into test_b (col1) values ('a'); insert into test_b (col1) values ('b'); insert into test_b (col1) values ('c'); insert into test_b (col1) values (null); Note: They key difference is that test_b contains a null value.
select * from test_a where col1 not in (select col1 from test_b); No rows returned
select * from test_a where not exists (select 1 from test_b where test_b.col1 = test_a.col1); Returns
col1 ==== d e 
4 Comments
hagrawal7777
@Gold: I think you should accept this answer, will help future visitors to directly get the bull's eye.
K.Nicholas
But is is being stated here, that any string matches null in SQL? This is a little confusing.
GenDemo
This is so wierd - I ran into this problem today. I swear they updated the function. I've used (and prefer) the 'not in' method countless times. Logically they should give the same answer.
GenDemo
ok - so one of my elves figured this out.. its because you have nulls in table test_b. if you comment out the insert statement that creates the null, then you'll see the 'not in' works.
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.
2 Comments
Nick Pierpoint
Don't forget about treatment of nulls: stackoverflow.com/questions/1699424/…
Doug Porter
The Tom Kyte link definitely gets to the root of the matter.
There can be performance differences, with exists being faster.
The most important difference is the handling of nulls. Your query might seem to work the same with both in and exists, but when your sub-query returns null you might get a shock.
You might find that the existence of nulls causes exists to fail.
See Joe Celko's 'SQL for smarties' for a better explanation of when to use each.
Comments
Not in is testing for the present of an element in a set of elements, so it is simpler.
Not exists can handle more complicated queries, including grouping (eg having sum(x)=z or having count(*)>3), results with multiple conditions (eg matching multiple elements), and can take advantage of indexes.
In some situations not in is easier to do than not exists. I generally find this is where I am testing for the value of a key field in set of values.
As a rule of the thumb, I prefer not exists as it covers a lot more situations than not in. Not exists can be used for every situation that not in is used for, but not the reverse.
