关于关联子查询--correlated subquery

最新推荐文章于 2024-12-30 09:51:46 发布

原创最新推荐文章于 2024-12-30 09:51:46 发布 · 2.8k 阅读

0 ·

CC 4.0 BY-SA版权

做程序员时积累的Oracle 专栏收录该内容

154 篇文章

订阅专栏

关联子查询在Oracle中是指嵌套子查询引用了来自父查询中任何层级的表格的列。每次处理父查询的一行时，都会评估一次子查询。它用于回答依赖于父查询每行处理值的多部分问题，例如找出薪水高于部门平均薪资的员工。子查询为每个部门计算平均工资，然后与父查询中的当前行进行比较，满足条件的行将被返回。

文档地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries007.htm#sthref3193

Using Subqueries

Oracle performs a correlatedsubquery when a nested subquery references a column from atable referred to a parent statement any number of levels above thesubquery. The parent statement can be a SELECT,UPDATE, or DELETE statement in which thesubquery is nested. A correlated subquery is evaluated once foreach row processed by the parent statement. Oracle resolvesunqualified columns in the subquery by looking in the tables namedin the subquery and then in the tables named in the parentstatement.

Acorrelated subquery answers a multiple-part question whose answerdepends on the value in each row processed by the parent statement.For example, you can use a correlated subquery to determine whichemployees earn more than the average salaries for theirdepartments. In this case, the correlated subquery specificallycomputes the average salary for each department.

See Also:

"UsingCorrelated Subqueries: Examples"

地址： http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066912

Using CorrelatedSubqueries: Examples The following examples show the generalsyntax of a correlated subquery:

SELECT select_list 
    FROM table1 t_alias1 
    WHERE expr operator 
        (SELECT column_list 
            FROM table2 t_alias2 
            WHERE t_alias1.column 
               operator t_alias2.column); 

UPDATE table1 t_alias1 
    SET column = 
        (SELECT expr 
            FROM table2 t_alias2 
            WHERE t_alias1.column = t_alias2.column); 

DELETE FROM table1 t_alias1 
    WHERE column operator 
        (SELECT expr 
            FROM table2 t_alias2 
            WHERE t_alias1.column = t_alias2.column);

The following statement returns data about employees whosesalaries exceed their department average. The following statementassigns an alias to employees, the table containingthe salary information, and then uses the alias in a correlatedsubquery:

SELECT department_id, last_name, salary 
   FROM employees x 
   WHERE salary > (SELECT AVG(salary) 
      FROM employees 
      WHERE x.department_id = department_id) 
   ORDER BY department_id;

For each row of the employees table, the parentquery uses the correlated subquery to compute the average salaryfor members of the same department. The correlated subqueryperforms the following steps for each row of theemployees table:---它的执行的步骤

The department_id of the row is determined.
The department_id is then used to evaluate theparent query.
If the salary in that row is greater than the averagesalary of the departments of that row, then the row isreturned.--从此处看，父查询在子查询确定了AVG(salary)以后，每一次都要执行比较，如果满足条件，那么父查询会将此行返回。即查询中有多少行，那么父查询就要执行多少次条件比较。

The subquery is evaluated once for each row of theemployees table.