oracle 相关子查询的使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1795039/
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
Use Of Correlated Subquery
提问by hrishi
Even though It has a performance issue, may I know what is the use of correlated subquery?
即使它有性能问题，我是否可以知道相关子查询的用途是什么？
采纳答案by David Aldridge
Well, firstly it doesn't have a performance issue. It is what it is, and it will be executed as well as possible given the performance constraints of the hardware and database structure.
嗯，首先它没有性能问题。它就是这样，在硬件和数据库结构的性能限制下，它会尽可能好地执行。
As for what it is useful for, it is just a way of expressing particular logical conditions.
至于它有什么用，它只是一种表达特定逻辑条件的方式。
回答by Tony Andrews
One common usage example: display details of the latest hired employee(s) for each department:
一个常见的用法示例：显示每个部门最近雇用的员工的详细信息：
select e.deptno, e.empno, e.ename, e.hiredate, e.sal
from emp e
where e.hiredate = (select max(e2.hiredate)
from emp e2
where e2.deptno = e.deptno -- the correlation
);
回答by Larry Lustig
A correlated subquery is used some action must be taken on each row in your query that depends on one or more values from that row.
使用相关子查询时，必须对查询中的每一行执行某些操作，这些操作取决于该行中的一个或多个值。
So, for instance, if you want to know include a row in a result set based on something happening in another table, you might do:
因此，例如，如果您想知道基于另一个表中发生的事情在结果集中包含一行，您可以这样做：
SELECT * FROM YourTable YT WHERE
EXISTS (SELECT * FROM SomeOtherTable SOT WHERE
SOT.ID = YT.ID AND SOT.SomeInteger BETWEEN YT.LowInteger AND YT.HighInteger)
Similarly in an UPDATE:
同样在 UPDATE 中：
UPDATE YourTable YT SET YourColumn =
(SELECT SUM(SomeColumn) FROM SomeOtherTable SOT
WHERE SOT.ID = YT.ID AND SOT.SomeField <> YT.SomeField)
Sometimes these queries can be written with standard JOINs, but sometimes not.
有时这些查询可以用标准的 JOIN 编写，但有时不能。
