What is a nested subquery?

What is a nested subquery?

• A. It is a subquery that runs independently of the outer query.
• B. It is a subquery contained within another subquery, where the outer query uses the inner results.
• C. It is a subquery that can only appear in the FROM clause.
• D. It is a subquery that always returns a single row.

Answer: (B)

A nested subquery is a subquery tucked inside another subquery, where the outer query uses the inner query’s results to decide what to return. The inner query may run on its own or depend on values from the outer query (a correlated subquery), and it can yield a single value or multiple values depending on how it’s used. For example, an outer query might select employees whose department is in the set produced by the inner query: select name from employees where dept_id in (select dept_id from departments where location = 'NY'). Here the inner subquery feeds the outer condition. A correlated version shows the inner query referencing values from the outer query for each row: select e.name from employees e where e.salary > (select avg(salary) from employees where dept_id = e.dept_id). The inner query uses e.dept_id from the outer query, making it depend on each row. So the best description is a subquery contained within another subquery, with the outer query using the inner results. The other statements either misstate where subqueries can appear or assume a fixed single-row result, which isn’t always the case.

A nested subquery is a subquery tucked inside another subquery, where the outer query uses the inner query’s results to decide what to return. The inner query may run on its own or depend on values from the outer query (a correlated subquery), and it can yield a single value or multiple values depending on how it’s used.

For example, an outer query might select employees whose department is in the set produced by the inner query: select name from employees where dept_id in (select dept_id from departments where location = 'NY'). Here the inner subquery feeds the outer condition.

A correlated version shows the inner query referencing values from the outer query for each row: select e.name from employees e where e.salary > (select avg(salary) from employees where dept_id = e.dept_id). The inner query uses e.dept_id from the outer query, making it depend on each row.

So the best description is a subquery contained within another subquery, with the outer query using the inner results. The other statements either misstate where subqueries can appear or assume a fixed single-row result, which isn’t always the case.