Correlated Subquery

Subquery which is depending on outer query output is called correlated subquery. First outer query is executed, based on the output, subquery is executed again based on the output, outer query is executed 2nd time.

In other words, a correlated subquery is a subquery that references a column from a table in the outer query, and uses that value as a condition in the subquery. The subquery is executed for each row returned by the outer query, so it is dependent on the data in the outer query.

1) Find the employees whose salary is higher than the average salary of their department.

select * from emp where sal >

(select avg(sal) from emp 

where deptno = emp.deptno)

2) Find the customers who have made more than one purchase in the past month.

SELECT *

FROM customers c

WHERE (

    SELECT COUNT(*)

    FROM orders o

    WHERE o.customer_id = c.customer_id

    AND o.order_date >= DATEADD(month, -1, GETDATE())

) > 1