Friday, November 24, 2006

IN Vs Exist in SQL

IN Vs EXISTS

The two are processed quite differently.


IN Clause

Select * from T1 where x in ( select y from T2 )

is typically processed as:

select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;

The sub query is evaluated, distinct, indexed and then
joined to the original table -- typically.


As opposed to "EXIST" clause

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


for x in ( select * from t1 )
loop
if ( exists ( select null from t2 where y = x.x )
then
OUTPUT THE RECORD
end if
end loop

It always results in a full scan of T1 whereas the first query can make use of
an index on T1(x).


So, when is exists appropriate and in appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the table T1 is relatively small and
executing ( select null from t2 where y = x.x ) is fast (nice index on
t2(y)). Then the exists will be faster as the time to full scan T1 and do the
index probe into T2 could be less then the time to simply full scan T2 to build
the subquery we need to distinct on.


Lets say the result of the subquery is small -- then IN is typicaly more
appropriate.


If both the subquery and the outer table are huge -- either might work as well
as the other -- depends on the indexes and other factors.

I wrote this note after searching on same issue and found a simple explanation by Tom Kyte.
Here is the link of original

9 comments:

Askvennela said...

yes, It's very nice explanations.

PKS said...

A good explanation about differences between exists and in

Timothy Chung said...

I recall one of my colleagues telling me that there is a item limit (500) on "IN" statement for Oracle 9i.

You can have a test on your DBMS. Hope it helps. :-)

Timothy Chung said...

The limit is on a String based IN statement (i.e. "SELECT * FROM tbl_a A WHERE A.id IN (0001, 0002 ... 0501)")

Pedro Cristian said...

in fact the limit is 1000.
ORA-01795: maximum number of expressions in a list is 1000


I enjoy the explanation on this error :
http://ora-1795.ora-code.com/
talking about 256 column.. when this is not related....

Unknown said...

Nice Explanation. Bookmarked :)
The original article was not as clear as yours for me..

Anonymous said...

Hello Sachin,

The explaination by you is very easy and nice to understand.

Regards,
Sachin.

Developer Knowledge House said...

Nice description. And one thing where to best use in and exists.

Anonymous said...

Abhinav Narayan

it is of good help.