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:
   
   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.

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, XML, Windows, Java, Adobe, HTML5, JavaScript, Angular, and much more. Check out our complete course catalog.

Categories

• Database Oracle

Courses

• Advanced Oracle SQL Queries see Class Schedule

Stephen has over 30 years' experience in training, development, and consulting in a variety of technology areas including Java, C, C++, XML, JavaScript, AJAX, Tomcat, JBoss, Oracle, and DB2. His background includes design and implementation of business solutions on client/server, Web, and enterprise platforms. Stephen is a published writer in both technical and non-technical endeavors. Stephen received an undergraduate degree in Computer Science and Physics from Florida State University.