SQL EXISTS 在 Oracle 中如何工作，它与 IN 有何不同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8875355/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does EXISTS work in Oracle, and how does it differ from IN?
提问by lamwaiman1988
I have trouble understanding the clause Exists while reading a oracle book. Here is 3 picture I took from the book.
在阅读 Oracle 书籍时，我无法理解 Exists 子句。这是我从书中拍的3张照片。
This is the 1st sql statement using the IN clause, I have no trouble understanding it.
这是第一个使用 IN 子句的 sql 语句，我理解它没有问题。
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.
这是使用 EXISTS 子句的第二个 sql 语句，我不明白为什么它在 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.
这是第三个 sql 语句，它与第一个 sql 语句获得相同的行，它需要两个表的额外连接，我无法推理。
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?
我试过谷歌“EXISTS ORACLE”，但大部分页面都在解释 EXISTS 和 IN 之间的区别，但没有解释 EXISTS 是如何工作的。麻烦大家解释一下吗？
回答by AlexanderZ
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
这是对两者以及如何决定使用哪个的非常详细的解释：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
Exists 检查子查询是否返回结果。基本上，如果您要获取子查询并自行执行它，如果它至少返回一行，则条件为真。第三个查询添加了第二个条件，将子查询链接到父查询，因此它检查特定人员是否有 deptno > 20 的部门
回答by jmoreno
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.
第二个查询返回所有行，因为它与主表不相关（相关），并且结果集大于0。第三个查询是相关子查询（E.DeptNo=），这就是为什么它重新调整与第一个查询相同的结果。
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.
EXIST 子句表示运行查询，一旦找到第一个匹配项就停止。如果找到匹配项，则返回 true，否则返回 false。该查询不需要（在您的第二种情况下不需要）与主查询相关。
