How does EXISTS work in Oracle, and how does it differ from IN?

Question

I have trouble understanding the clause Exists while reading a oracle book. Here is 3 picture I took from the book.

This is the 1st sql statement using the IN clause, I have no trouble understanding it.

This is the 2nd sql statement using the EXISTS clause, I don't understand why it return all rows while there is a condition of DEPTNO > 20.

This is the 3rd sql statement which are getting the same rows as the 1st sql statement, it require a extra join of the two table and I cannot reason it.

I tried google "EXISTS ORACLE" but most of the page are explaining the difference between EXISTS and IN, but not explaining how does EXISTS work. Would you guys bother to explain it?

Answer 1

Here is a pretty detailed explanation of both and how to decide which one to use: http://www.techrepublic.com/article/oracle-tip-understand-the-difference-between-in-and-exists-in-subqueries/5297080

Exists checks whether the subquery returns a result. Basically, if you were to take the subquery and execute it by itself, if it returns at least one row, then the condition is true. The third query adds a second condition that links the subquery to the parent query, thus it checks whether specific person has a department with deptno > 20

Answer 2

The second query returns all rows because it is not correlated (related) to the primary table, and the result set is larger than 0. The third query is a correlated sub query ( the E.DeptNo=), which is why it retuns the same result as the first query.

The EXIST clause says to run the query, stopping as soon as it finds the first match. If it finds a match, it returns true, if not it returns false. The query does not need (and in your second case is not) to be related to the primary query.