MySQL Correlated Subqueries Explained

Write a Correlated Subquery to Find Departments with Employees Earning Above Average

Using the `employees` table, write a query that lists departments where at least one employee earns more than the average salary of that department.

Expected output: A list of department IDs or names meeting the condition.

Hint: Use EXISTS with a correlated subquery referencing the outer department.

Rewrite a Correlated Subquery as a JOIN

Take the example correlated subquery that finds employees earning above their department average and rewrite it using JOIN and GROUP BY.

Expected output: Equivalent result set using JOIN instead of a correlated subquery.

Hint: Calculate average salaries per department in a derived table and join it with employees.

A correlated subquery is a subquery that references columns from the outer query and executes once for each row processed by the outer query.

A regular subquery runs once independently, while a correlated subquery depends on the outer query and runs repeatedly for each outer row.

Avoid correlated subqueries when they cause performance issues on large datasets; consider using JOINs or derived tables instead.