How can you optimize a correlated subquery that counts employees per department to avoid repeated execution?

hard📝 Application Q9 of 15

PostgreSQL - Subqueries in PostgreSQL

AAdd DISTINCT inside the subquery to reduce duplicates.

BRewrite using JOIN and GROUP BY to calculate counts once per department.

CUse a window function inside the subquery.

DReplace the subquery with a scalar function call.

Step-by-Step Solution

Solution:

Step 1: Identify the problem with correlated subqueries
They run once per outer row, causing repeated work.
Step 2: Use JOIN and GROUP BY to calculate counts once
JOIN with GROUP BY aggregates counts per department efficiently.
Final Answer:
Rewrite using JOIN and GROUP BY to calculate counts once per department. -> Option B
Quick Check:
Optimization by JOIN and GROUP BY = Rewrite using JOIN and GROUP BY to calculate counts once per department. [OK]

Quick Trick: JOIN with GROUP BY often replaces correlated subqueries for efficiency. [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Want More Practice?

15+ quiz questions · All difficulty levels · Free