what is the difference between sub-query and co-related sub-query?

by

Sub-query

The inner query execute first, the outer query deprnds on inner query output.

Correlated sub-query

The outer query execute first, the inner query deprnds on outer query result.

A correlated sub-query is where the sub-query part makes reference to values from the outer query.
------------------------------------------------------------------------------------------------------------------------------

SELECT emp1.emp_id, emp1.last_name, emp1.job_id, emp1.department_id from employee emp1

WHERE EXISTS (SELECT ‘X’ FROM employee emp2 WHERE emp2.manager_id = emp1.emp_ID);

Is a correlated sub-query because of the reference to emp1.emp_ID in the sub- query. The sub-query is evaluated once for each row processed by the outer query.

------------------------------------------------------------------------------------------------------------------------------

SELECT e1.employee_number, e1.name FROM employee e1

WHERE e1.salary > (SELECT avg(e2.salary) FROM e2.employee);

Is not a correlated sub-query because the sub-query makes no reference to the values of the outer part of the query.