Correlated Subqueries

ALSO CHECK: All About Database

A correlated subquery is a subquery that uses the values of the outer query, i.e it depends on the outer query for its values. Because of this dependency, a correlated subquery cannot be executed independently as a simple subquery.

Correlated subqueries are used for row-by-row processing. Each subquery is executed once for every row of the outer query. e.g:

SELECT t1.column1, t1.column2,...
FROM table1 t1 WHERE
t1.column1 operator(
   SELECT t2.column1 FROM table2 t2
   WHERE t2..something=t1.something);

What is the difference between Nested Subqueries and Correlated Subqueries?
In nested subquery, the inner SELECT query runs first and executes only once, returning values to be used by the main query. Where as a correlated subquery, executes once for each candidate row considered by the outer query. In other words, in correlated subqueries the inner query is driven by the outer query.

Can we use the ANY and ALL operator in a correlated subquery?
Yes!

Correlated subquery example
Lets say we have a Employee table, which has employee id, employee name, salary and department id columns. Write a query to find the employee whose salary is higher than the average salary of their department?

SELECT o.emp_id, o.emp_name, o.salary
FROM employees o
WHERE o.salary > (
   SELECT AVG(i.salary) FROM employees i
   WHERE i.department_id=o.department_id);

-K Himaanshu Shuklaa..