With a normal nested sub query, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated sub query however, executes once for each candidate row considered by the outer query. In other words the inner query is driven by the outer query.
Steps of execution:
Nested sub query execution:
- The inner query is executed first and finds a value
- The outer query executes once, using the value from the inner query.
Correlated Sub query execution:
- Get the candidate row (fetched by the outer query).
- Execute the inner query using the value of the candidate row.
- Use the values resulting from the inner query to test “qualify or disqualify” the candidate.
- Repeat until no candidate row remains tested.
The general format of a correlated sub query is:
select column1, column2, . . . . . . .
from table1 outer
where [column1] operator (select column1, column2,
from table2 where expr1= outer.expr2);