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
See Also:"Using Correlated Subqueries: Examples"
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
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
department_idof the row is determined.
department_idis then used to evaluate the parent query.
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