Correlated Subquery — Concept Check
Questions on Correlated Subquery. A correlated subquery references columns from the outer query and re-executes once per outer row, enabling row-by-row comparisons at the cost of potential N+1 performance problems.
Questions in this quiz
7 questions on Correlated Subquery — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A query is running slowly because it contains a subquery that references a column from the outer query. What is likely causing the performance issue?
- AThe subquery uses EXISTS, which is inefficient for large datasets.
- BThe subquery is independent and runs once, causing a large temporary table.
- CThe subquery is correlated and runs for each outer row, causing N+1 performance problems.
- DThe subquery uses a window function, which is inherently slow.
2.You need to check if users have placed at least one order. Which query is the most efficient?
- AUse COUNT(*) in a subquery to count orders.
- BUse EXISTS in a subquery to short-circuit on the first match.
- CUse IN with a subquery to list user IDs.
- DUse a JOIN to combine users and orders tables.
3.A developer rewrites a correlated subquery using a JOIN. What is the expected outcome?
- AIncreased performance overhead due to additional joins.
- BDecreased performance due to complex query planning.
- CImproved performance due to reduced subquery executions.
- DNo change in performance as both methods are equivalent.
4.Why might a database optimizer fail to unnest a correlated subquery?
- AThe subquery is too complex for the optimizer to transform.
- BThe subquery is independent and does not need unnesting.
- CThe subquery uses a window function, which cannot be unnested.
- DThe subquery uses EXISTS, which is incompatible with unnesting.
5.A query uses a correlated subquery to find employees earning more than their department's average salary. What is a potential issue?
- AThe query will return incorrect results if salaries are NULL.
- BThe query will not work if departments have no employees.
- CThe query will fail if there are no departments.
- DThe query might be slow due to N+1 execution pattern.
6.What is a key advantage of using EXISTS in a subquery?
- AIt counts all matches before returning a result.
- BIt short-circuits on the first match, improving efficiency.
- CIt guarantees a full scan of the outer table.
- DIt materializes the subquery into a list for faster access.
7.In a query with a correlated subquery, what should you check to understand its performance impact?
- AThe use of window functions in the subquery.
- BThe number of columns in the subquery.
- CThe presence of aggregate functions in the subquery.
- DThe EXPLAIN plan to see if the subquery is unnested.