Correlated subqueries

 

A correlated subquery is a subquery that SQL might need to re-evaluate when it examines each new row (the WHERE clause) or each group of rows (the HAVING clause) in the outer-level SELECT statement.

• Correlated names and references
  A correlated reference can appear in a search condition in a subquery. The reference is always in the form of X.C, where X is a correlation name and C is the name of a column in the table that X represents.

• Example: Correlated subquery in a WHERE clause
  Suppose that you want a list of all the employees whose education levels are higher than the average education levels in their respective departments. To get this information, SQL must search the CORPDATA.EMPLOYEE table.

• Example: Correlated subquery in a HAVING clause
  Suppose that you want a list of all the departments whose average salaries are higher than the average salaries of their areas (all departments whose WORKDEPT begins with the same letter belong to the same area). To get this information, SQL must search the CORPDATA.EMPLOYEE table.

• Example: Correlated subquery in a select-list
  Suppose that you want a list of all the departments, including the department name, number, and manager's name.

• Example: Correlated subquery in an UPDATE statement
  When you use a correlated subquery in an UPDATE statement, the correlation name refers to the rows that you want to update.

• Example: Correlated subquery in a DELETE statement
  When you use a correlated subquery in a DELETE statement, the correlation name represents the row that you want to delete. SQL evaluates the correlated subquery once for each row in the table named in the DELETE statement to decide whether to delete the row.

 

Parent topic:

Using subqueries