what is a correlated subquery code example

Example 1: what are correlated and non correlated subquery

Noncorrelated Subqueries
A noncorrelated subquery executes independently of the outer query. The subquery executes first, and then passes its results to the outer query, For example:

=> SELECT name, street, city, state FROM addresses WHERE state IN (SELECT state FROM states);

Correlated Subqueries
A correlated subquery typically obtains values from its outer query before it executes. When the subquery returns, it passes its results to the outer query.

=> SELECT name, street, city, state FROM addresses
     WHERE EXISTS (SELECT * FROM states WHERE states.state = addresses.state);

Example 2: correlated subquery

A correlated subquery is a way of reading values in each row
and comparing those values in each row with related data.

A correlated subquery is evaluated once for each row
processed by the parent query.
i.e. the subquery returns a value for each row
processed by the parent query unlike the normal subquery which executes
only once and returns the data to the parent query for processing.

SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department_id = outer.department_id
               );

Here the inner query (correlated subquery) generates
the avg salary for each department_id processed by the outer query.