How to Use Correlated Subqueries
A correlated subquery is a select statement contained within an outer select statement that refers to a column in the outer select statement. The correlated subquery is executed for each row selected in the outer select and its result is available within the where clause of the outer select.
To learn how to use correlated subqueries, follow these steps:
- You'll need to download and install Oracle 12c. The instructions for the setup can be found in How to use sample schemas. Follow steps 1 through 4 in this topic before proceeding to the next step.
- We will illustrate a correlated subquery by selecting the employees in the
Employeestable that earn a salary that is greater than the average salary of employees in the same department. Execute the following SQL select statement:
Note that I have displayed the first part of the output that contains 38 employees overall. I have highlighted the correlated subquery and I have pointed out the correlation name (outer_employee) that the subquery references. The correlation name ensures that the average salary calculation will only apply to employees in the same department as the employee selected in the outer query. The subquery is enclosed within parentheses and returns the result of theavgaggregate function.