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:

  1. 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.
  2. We will illustrate a correlated subquery by selecting the employees in the Employees table that earn a salary that is greater than the average salary of employees in the same department. Execute the following SQL select statement:
    Display employees with salary greater than average salary in the same department
    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 the avg aggregate function.
Author: Stephen Withrow

Stephen has over 30 years of experience in training, development, and consulting in a variety of technology areas including Python, Java, C, C++, XML, JavaScript, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen has a degree in Computer Science and Physics from Florida State University.

About Webucator

Webucator provides instructor-led training to students throughout the US and Canada. We have trained over 90,000 students from over 16,000 organizations on technologies such as Microsoft ASP.NET, Microsoft Office, Azure, Windows, Java, Adobe, Python, SQL, JavaScript, Angular and much more. Check out our complete course catalog.