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.