Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
May 16, 2000

Advantages of EXISTS over IN Clause in a Query

If you wish to retrieve from the department table all the department numbers that have at least one employee assigned to them, write the query as:
 select deptno, deptname 
from   dept 
where  deptno in 
	 ( select deptno 
   from   emp) ;
This query will run a full table scan on both the emp and dept tables. Even if there was an index on the deptno column of emp table, the sub-query would not be using the index and, hence, performance would suffer.

We can rewrite the query as:

 select deptno, deptname 
from   dept 
where  exists in 
	 ( select deptno 
   from   emp
   where  dept.deptno = emp.deptno) ;
This query uses an existing deptno index on the emp table, making the query much faster. Thus, wherever possible use EXISTS in place of IN clause in a query.
Jayanta Sengupta
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap