http://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx

a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from outer query. The subquery is evaluated once for each row processed by the outer queryHere is an example for a typical correlated subquery. In this example we are finding the list of all employees whose salary is above average for their departments.SELECT employee_number, nameFROM employees AS BobWHERE salary > (SELECT AVG(salary)FROM employeesWHERE department = Bob.department); In the above query the outer query isSELECT employee_number, nameFROM employees AS BobWHERE salary > ... and the inner query (the correlated subquery) isSELECT AVG(salary)FROM employeesWHERE department = Bob.department In the above nested query the inner query has to be re-executed for each employee. (A sufficiently smart implementation may cache the inner query's result on a department-by-department basis, but even in the best case the inner query must be executed once per department. See "Optimizing correlated subqueries" below.)Correlated subqueries may appear elsewhere besides the WHERE clause; for example, this query uses a correlated subquery in the SELECT clause to print the entire list of employees alongside the average salary for each employee's department. Again, because the subquery is correlated with a column of the outer query, it must be re-executed for each row of the result.SELECTemployee_number,name,(SELECT AVG(salary) FROM employeesWHERE department = Bob.department) AS department_averageFROM employees AS Bob;

A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.

http://asp-net-corner.blogspot.in/