select语句中的子查询如何在oracle中工作 [英] How does Subquery in select statement work in oracle
问题描述
我想知道你如何在oracle中的select语句中使用子查询,它到底输出了什么。
例如,如果我有一个查询想要显示雇员的姓名和他们从这些表中管理的档案数量。
雇员(EmpName,EmpId)
Profile(ProfileId,...,EmpId)
如何使用子查询?
在select语句中需要一个子查询来实现按功能分组来统计每个员工管理的配置文件的数量,但我不太确定。
很简单 -
SELECT empname,
empid,
(SELECT COUNT(profileid)
FROM profile
WHERE profile.empid = employee.empid)
AS number_of_profiles
FROM员工;
使用如下表连接更简单:
SELECT e.empname,e.empid,COUNT(p.profileid)AS number_of_profiles
FROM employee e LEFT JOIN配置文件p ON e.empid = p .empid
GROUP BY e.empname,e.empid;
子查询解释: 编辑: 当在 左连接的解释 左连接非常方便,特别是当您要替换 有关更多信息,请阅读关于子查询的Oracle Docs 和左连接或左连接。 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. I would like to know how do you use a subquery in a select statement in oracle and what exactly does it output. 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) Profile(ProfileId, ..., 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. It's simple- It is even simpler when you use a table join like this:
Explanation for the subquery: Essentially, a subquery in a Edit: When you use a subquery in a Explanation for the left join A left join is very handy, especially when you want to replace the For more information read Oracle Docs on subqueries and left join or left outer join. 这篇关于select语句中的子查询如何在oracle中工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
本质上, select中的子查询选择
获取标量值并将其传递给主查询。 select中的子查询
不允许传递多个行和多个列,这是一个限制。在这里,我们将一个 count
传递给主查询,正如我们所知,这个查询总是只有一个数字 - 一个标量值。如果未找到值,则子查询将 null
返回到主查询。此外,子查询可以从主查询的子句的子句中访问列,如我的查询所示,其中
employee.empid
从外部查询传递到内部查询。
中使用子查询时,选择
子句时,Oracle基本上将其视为左连接(您可以在为您的查询解释计划),行的基数为右边的每一行右边都有一个。
select
子查询时,由于其限制。这里没有限制 LEFT JOIN
关键字两边表格的行数。
SELECT empname,
empid,
(SELECT COUNT (profileid)
FROM profile
WHERE profile.empid = employee.empid)
AS number_of_profiles
FROM employee;
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;
select
gets a scalar value and passes it to the main query. A subquery in select
is not allowed to pass more than one row and more than one column, which is a restriction. Here, we are passing a count
to 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 null
to the main query. Moreover, a subquery can access columns from the from
clause of the main query, as shown in my query where employee.empid
is passed from the outer query to the inner query.
select
clause, Oracle essentially treats it as a left join (you can see this in the explain plan for your query), with the cardinality of the rows being just one on the right for every row in the left.
select
subquery due to its restrictions. There are no restrictions here on the number of rows of the tables in either side of the LEFT JOIN
keyword.