Correlated Subqueries The subquery references a column from a table in the

Correlated subqueries the subquery references a

This preview shows page 213 - 216 out of 380 pages.

Correlated Subqueries The subquery references a column from a table in the parent query. SELECT column1 , column2 , ... FROM table1 WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = .expr2 ); outer outer Correlated Subqueries (continued) A correlated subquery is one way of reading every row in a table and comparing values in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. That is, you use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement. The Oracle server performs a correlated subquery when the subquery references a column from a table in the parent query. Note: You can use the ANY and ALL operators in a correlated subquery.
Image of page 213
Oracle Database 10 g : SQL Fundamentals II 6-12 Copyright © 2006, Oracle. All rights reserved. SELECT last_name, salary, department_id FROM employees outer WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id); Using Correlated Subqueries Find all employees who earn more than the average salary in their department. Each time a row from the outer query is processed, the inner query is evaluated. Using Correlated Subqueries The example in the slide determines which employees earn more than the average salary of their department. In this case, the correlated subquery specifically computes the average salary for each department. Because both the outer query and inner query use the EMPLOYEES table in the FROM clause, an alias is given to EMPLOYEES in the outer SELECT statement for clarity. The alias makes the entire SELECT statement more readable. Without the alias, the query would not work properly because the inner statement would not be able to distinguish the inner table column from the outer table column.
Image of page 214
Oracle Database 10 g : SQL Fundamentals II 6-13 Copyright © 2006, Oracle. All rights reserved. Using Correlated Subqueries Display details of those employees who have changed jobs at least twice. SELECT e.employee_id, last_name,e.job_id FROM employees e WHERE 2 <= (SELECT COUNT(*) FROM job_history WHERE employee_id = e.employee_id); Using Correlated Subqueries (continued) The example in the slide displays the details of those employees who have changed jobs at least twice. The Oracle server evaluates a correlated subquery as follows: 1. Select a row from the table specified in the outer query. This will be the current candidate row. 2. Store the value of the column referenced in the subquery from this candidate row. (In the example in the slide, the column referenced in the subquery is E.EMPLOYEE_ID .) 3. Perform the subquery with its condition referencing the value from the outer query’s candidate row. (In the example in the slide, the COUNT(*) group function is evaluated based on the value of the E.EMPLOYEE_ID column obtained in step 2.) 4. Evaluate the WHERE clause of the outer query on the basis of results of the subquery performed in step 3. This determines whether the candidate row is selected for output. (In
Image of page 215
Image of page 216

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture