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

Question

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.

Answer 1

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 2

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 3

EXISTS returns Boolean and IN returns values