What is the difference between IN subselects and EXISTS subselect?

If there is an index on the attributes tested an IN is more efficient since DB2 uses the index for the IN. (IN for index is the mnemonic).
  • Interview Candidate
  • 12
  • 9186

Showing Answers 1 - 12 of 12 Answers

Vipin Sharma

  • Jan 1st, 2007
 

IN is used for simple join queries but exists is used in correlated join queries generally, both are used as a filter for searching.

  Was this answer useful?  Yes

saibabu

  • Jan 11th, 2007
 

hi  vipin sharma,

IN subselects  can also uesd in corelated sub queries.

IN subselects  needs  to specify  the vlues u want to search.

EXISTS    subselects  doesn't need any to specify values . it searches for  existance of  return   values  from the select  statement, if exists the where  commits to true and gives the output.  

  Was this answer useful?  Yes

nlarsensmith

  • Feb 22nd, 2011
 

***IN***
IN subselects perform search on a list of values returned by the subquery.
ex)
select * from table1 where col1 
IN
(select col1 from table2 where <some condition>)

-Based on the range of result(s) returned by subquery the outer query performs selection

***EXISTS***
Can be interpreted as "If any value is retuened by subquery, then perform outer query search"
ex)
update table1 set col1="True" where col2="XXX" AND EXISTS (select * from table2 where col3="YYY")

-If there is any result set for the subquery then update is performed else it is not

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions