SQL oracle中select语句中的子查询是如何工作的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20557899/
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 Subquery in select statement work in oracle
提问by user3054901
I have looked all over for an explanation, to how does the subquery in a select statement work and still I cannot grasp the concept because of very vague explanations.
我已经四处寻找解释，关于 select 语句中的子查询如何工作，但由于解释非常模糊，我仍然无法理解这个概念。
I would like to know how do you use a subquery in a select statement in oracle and what exactly does it output.
我想知道您如何在 oracle 的 select 语句中使用子查询以及它究竟输出什么。
For example, if i had a query that wanted to display the names of employees and the number of profiles they manage from these tables
例如，如果我有一个查询想要显示员工的姓名以及他们从这些表中管理的个人资料数量
Employee(EmpName, EmpId)
员工（EmpName，EmpId）
Profile(ProfileId, ..., EmpId)
配置文件（配置文件 ID，...，EmpId）
how do I use the subquery?
如何使用子查询？
I was thinking a subquery is needed in the select statement to implement the group by function to count the number of profiles being managed for each employee, but I am not too sure.
我想在 select 语句中需要一个子查询来实现 group by 函数来计算每个员工管理的配置文件的数量，但我不太确定。
回答by Rachcha
It's simple-
这很简单-
SELECT empname,
empid,
(SELECT COUNT (profileid)
FROM profile
WHERE profile.empid = employee.empid)
AS number_of_profiles
FROM employee;
It is even simpler when you use a table join like this:
当您使用这样的表连接时，它甚至更简单：
SELECT e.empname, e.empid, COUNT (p.profileid) AS number_of_profiles
FROM employee e LEFT JOIN profile p ON e.empid = p.empid
GROUP BY e.empname, e.empid;
Explanation for the subquery:
子查询说明：
Essentially, a subquery in a selectgets a scalar value and passes it to the main query. A subquery in selectis not allowed to pass more than one row and more than one column, which is a restriction. Here, we are passing a countto the main query, which, as we know, would always be only a number- a scalar value. If a value is not found, the subquery returns nullto the main query. Moreover, a subquery can access columns from the fromclause of the main query, as shown in my query where employee.empidis passed from the outer query to the inner query.
本质上，a 中的子查询select获取一个标量值并将其传递给主查询。一个子查询中select不允许传递多于一行和多于一列，这是一个限制。在这里，我们将 a 传递count给主查询，正如我们所知，它始终只是一个数字——一个标量值。如果未找到值，则子查询返回null到主查询。此外，子查询可以从from主查询的子句访问列，如我的查询所示，其中employee.empid从外部查询传递到内部查询。
Edit:
编辑：
When you use a subquery in a selectclause, Oracle essentially treats it as a left join (you can see this in the explain planfor your query), with the cardinality of the rows being just one on the right for every row in the left.
当您在select子句中使用子查询时，Oracle 本质上将其视为左联接（您可以在查询的解释计划中看到这一点），行的基数对于左侧的每一行都在右侧。
Explanation for the left join
左连接的说明
A left join is very handy, especially when you want to replace the selectsubquery due to its restrictions. There are no restrictions here on the number of rows of the tables in either side of the LEFT JOINkeyword.
左连接非常方便，尤其是当您select由于其限制而想要替换子查询时。此处对LEFT JOIN关键字任一侧的表的行数没有限制。
For more information read Oracle Docs on subqueriesand left join or left outer join.
回答by lambdakiki
In the Oracle RDBMS, it is possible to use a multi-row subquery in the select clause as long as the (sub-)output is encapsulated as a collection. In particular, a multi-row select clause subquery can output each of its rows as an xmlelement that is encapsulated in an xmlforest.
在 Oracle RDBMS 中，只要将（子）输出封装为集合，就可以在 select 子句中使用多行子查询。特别是，多行选择子句子查询可以将其每一行作为封装在 xmlforest 中的 xmlelement 输出。
