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

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

enter image description here 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.

enter image description here 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?

sql oracle-database
Edit
Report