Which two statements are true regarding the execution of the correlated subqueries?
(Choose two.)
A.
The nested query executes after the outer query returns the row.
B.
The nested query executes first and then the outer query executes.
C.
The outer query executes only once for the result returned by the inner query.
D.
Each row returned by the outer query is evaluated for the results returned by the
inner query.
B, D
A is incorrect: a subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
B is incorrect. In correlated subqueries the inner query is executed after the outer query. Think about this:
SELECT e.employee_id
FROM employees e
WHERE EXISTS (SELECT manager_id
FROM employees
WHERE department_id = e.department_id);
This simple example returns employee_id’s where exist managers in the same department.
So it wouldn’t be possible to evaluate the WHERE clause in the inner query without knowing which department_id is in the outer query’s row.
Your explanation for the answer A would be correct if the question was about ‘non-correlated’ subqueries.
A y D
Why D is correct ? i think C is correct