What is a correlated subquery and how does it differ from a regular subquery?

A correlated subquery references columns from the outer query and executes once per outer row. Regular subqueries are independent and execute once. Correlated subqueries are slower but necessary for row-specific logic.

Why are correlated subqueries slow and what are the alternatives?

They execute once per outer row (N executions for N rows). Alternatives: JOINs with GROUP BY, window functions, or CTEs. These alternatives process data in sets rather than row-by-row.

Write a correlated subquery to find employees earning above their department average

SELECT name, department, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department);

Subquery references e.department from outer query, calculating department average for each employee.