Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
Which is more faster - IN or EXISTS?
Which is more faster - IN or EXISTS?
✍: Guest
Well, the two are processed very differently.
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’ed, indexed (or hashed or sorted) and then joined to the original table — typically. As opposed to
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 where exists appropriate and in appropriate? Lets say the result of the sub query ( 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 very 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 sub query we need to distinct on.
Lets say the result of the sub query is small — then IN is typically more appropriate. If both the sub query and the outer table are huge — either might work as well as the other — depends on the indexes and other factors.
2011-12-05, 3147👍, 0💬
Popular Posts:
How did you implement UML in your project ? PART II Implementation phase / Coding phase (Class diagr...
How To Create Nested Tables? - XHTML 1.0 Tutorials - Understanding Tables and Table Cells You can cr...
What is SMC approach of estimation?
How To Check the Oracle TNS Settings? - Oracle DBA FAQ - ODBC Drivers, DSN Configuration and ASP Con...
How To Insert Multiple Rows with a SELECT Statement? - MySQL FAQs - Managing Tables and Running Quer...