Correlate Subqueries

In SQL, a correlated subquery is a subquery that uses values from the outer query in its WHERE clause. The correlated subquery is evaluated once for each row processed by the outer query. It exists because it depends on the outer query and it cannot execute independently of the outer query because the subquery is correlated with the outer query as it uses its column in its WHERE clause.

SELECT column_name [, column_name...]
FROM   table1 [, table2...]
WHERE  column_name OPERATOR
  (SELECT column_name [, column_name...]
   FROM table_name
   WHERE condition [table1.column_name = table2.column_name...]);

SELECT e1.employee_name, e1.salary
FROM employee e1
WHERE salary > 
   (SELECT AVG(salary)
   FROM employee e2
   WHERE e1.department = e2.department);