Tuesday, 4 September 2012

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


Related Posts Plugin for WordPress, Blogger...