[Solved] Identify the error in this correlated subquery:SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM employees e1 WHERE e1.department_id = e1.department_id); | PostgreSQL

PostgreSQL - Subqueries in PostgreSQL

Identify the error in this correlated subquery:

SELECT e1.name FROM employees e1 WHERE e1.salary > (SELECT AVG(salary) FROM employees e1 WHERE e1.department_id = e1.department_id);

AThe query is missing a GROUP BY clause.

BThe subquery is missing a WHERE clause.

CThe subquery references a non-existent column.

DThe inner query alias shadows the outer query alias causing incorrect correlation.

Step-by-Step Solution

Solution:

Step 1: Analyze alias usage in inner and outer queries
Both inner and outer queries use alias e1, causing the inner query to reference itself only.
Step 2: Understand impact on correlation
Because of alias shadowing, the subquery condition e1.department_id = e1.department_id is always true and not correlated to outer row.
Final Answer:
The inner query alias shadows the outer query alias causing incorrect correlation. -> Option D
Quick Check:
Use different aliases to avoid shadowing [OK]

Quick Trick: Use distinct aliases for outer and inner queries [OK]

Common Mistakes:

Master "Subqueries in PostgreSQL" in PostgreSQL

9 interactive learning modes - each teaches the same concept differently

Identify the error in this correlated subquery: