top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

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

+1 vote
565 views
What is the main difference between the IN and EXISTS clause in subqueries?
posted Aug 19, 2014 by Joshva

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

3 Answers

0 votes

EXISTS will tell you whether a query returned any results. eg:

SELECT * FROM Orders o WHERE EXISTS (
    SELECT * FROM Products p where p.ProductNumber = o.ProductNumber)

IN is used to compare one value to several, and can use literal values, like this:

SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100)

See More :https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:**********

answer Aug 20, 2014 by Madhavi Latha
0 votes

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.

answer Sep 1, 2014 by Rathi
0 votes

EXISTS returns Boolean and IN returns values

answer Sep 11, 2014 by Arun Gowda
Similar Questions
0 votes

What are the advantage and disadvantages of using a select for update in a cursor Using the "for update clause" and then using useing "update... for current cursor" is a good way to make sure that no one else is changing the rows you are working with.

...