关于关联子查询--correlated subquery

oracle sql

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

 

Using Subqueries

 

     Oracle performs a correlated subquery when a nested subquery references a column from a table referred to a parent statement any number of levels above the subquery. The parent statement can be a SELECT, UPDATE, or DELETE statement in which the subquery is nested. A correlated subquery is evaluated once for each row processed by the parent statement. Oracle resolves unqualified columns in the subquery by looking in the tables named in the subquery and then in the tables named in the parent statement.

    A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.

See Also:

"Using Correlated Subqueries: Examples"
\
地址:http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066912
 
 

Using Correlated Subqueries: Examples The following examples show the general syntax 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 whose salaries exceed their department average. The following statement assigns an alias to employees, the table containing the salary information, and then uses the alias in a correlated subquery:

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 parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the employees table:---它的执行的步骤

  1. The department_id of the row is determined.

  2. The department_id is then used to evaluate the parent query.

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

The subquery is evaluated once for each row of the employees table.


你的评论

就没有什么想说的吗?

最新博客

关于我们 免责声明 移动版

©2017传客网    琼ICP备15003173号-2    

本站部分文章来源于互联网,版权归属于原作者。
本站所有转载文章言论不代表本站观点,如是侵犯了原作者的权利请发邮件联系站长(weishubao@126.com),我们收到后立即删除。
站内所有资源仅供学习与参考,请勿用于商业用途,否则产生的一切后果将由您自己承担!

X