How does EXISTS work in Oracle and how is it different from IN?

Question

I have a problem understanding the situation. When reading an oracle book exists. Here are 3 images that I took from the book.

This is a 1st sql expression using an IN clause, I don't understand it.

This is a 2nd sql expression using the EXISTS clause, I don’t understand why it returns all rows while the DEPTNO> 20 condition exists.

This is the 3rd sql statement, which receives the same rows as the 1st sql operator, this requires an additional join of two tables, and I can not justify it.

I tried google "EXISTS ORACLE", but most of the page explains the difference between EXISTS and IN, but does not explain how EXISTS work. Could you explain this?

Answer 1

Here is a detailed explanation of how 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

Existing checks to see if the subquery returns a result. Basically, if you were to take a subquery and execute it on your own, 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, so it checks to see if a particular person has a department with deptno> 20

Answer 2

The second query returns all rows because it is not correlated (linked) with the main table, and the result set is greater than 0. The third query is a correlated subquery (E.DeptNo =), so it returns the same result as the first query.

The EXIST clause says to start a query, stopping as soon as it finds the first match. If it finds a match, it returns true, if it does not return false. The request is not needed (and in the second case not) to communicate with the main request.