Databases Reference
In-Depth Information
EMPLOYEE_ID LAST_NAME FIRST_NAME SALARY
----------- ------------- ------------------ ----------
158 McEwen Allan 9000
152 Hall Peter 9000
109 Faviet Daniel 9000
202 Fay Pat 6000
184 Sarchand Nandita 4200
5 rows selected.
Note that Janice also could have checked for a department ID other than 60,
as you have seen in previous queries.
Correlated Subqueries
looks very much like a garden-variety subquery, with one
important difference: The correlated subquery references a column in the main
query as part of the qualification process to see if a given row will be returned by
the query. For each row in the parent query, the subquery is evaluated to see if the
row will be returned. In Janice's situation, the salary of each individual employee
is compared to the average salary for that employee's department. The check-
marked rows in the parent query are returned.
A
correlated subquery
correlated subquery
A subquery that contains a reference to a
column in the main, or parent, query.
Correlation
Parent (outer) Query
Comparison Operator
>
Correlated (inner) Subquery
Janice knows that King will be asking for more queries regarding salaries, so
she comes up with a fairly generic query that will identify employees who are
