Nested Sub query VS Correlated Sub query

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);