correlated subquery

1A correlated subquery is a way of reading values in each row
2and comparing those values in each row with related data.
3
4A correlated subquery is evaluated once for each row
5processed by the parent query.
6i.e. the subquery returns a value for each row
7processed by the parent query unlike the normal subquery which executes
8only once and returns the data to the parent query for processing.
9
10SELECT last_name, salary, department_id
11FROM employees outer
12WHERE salary > (SELECT AVG(salary)
13                FROM employees
14                WHERE department_id = outer.department_id
15               );
16
17Here the inner query (correlated subquery) generates
18the avg salary for each department_id processed by the outer query.

1Noncorrelated Subqueries
2A noncorrelated subquery executes independently of the outer query. The subquery executes first, and then passes its results to the outer query, For example:
3
4=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state FROM states);
5
6Correlated Subqueries
7A correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.
8
9=> SELECT name, street, city, state FROM addresses
10     WHERE EXISTS (SELECT * FROM states WHERE states.state = addresses.state);