- 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.
0 comments:
Post a Comment