What is the main difference between the IN and EXISTS clause in subqueries??

The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.

IN -- The inner query is executed first and the list of values obtained as its result is used by the outer query.The inner query is executed for only once.

EXISTS -- The first row from the outer query is selected ,then the inner query is executed and , the outer query output uses this result for checking.This process of inner query execution repeats as many no.of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many no.of times.

rajanipriya
Profile Answers by rajanipriya Questions by rajanipriya
May 10th, 2006
7
69003

Questions by rajanipriya answers by rajanipriya

SQL

Answer

Showing Answers 1 - 7 of 7 Answers

Rajeshwaran

Jun 3rd, 2006

In operator test for the particular value in the subquery and exist operator is a boolean operator.so it works more efficient and faster than In operator

Mahesh

Jul 24th, 2007

In operaor scan all the values inside the IN block where as EXIST quit after 1st occurrence.

gemini burj
Profile Answers by gemini burj

Oct 23rd, 2007

In case of a big number of list from the select list IN operator should not be used but the exists shall be used with the proper condition this will definetely make the query optimal query.