What is a subquery and what is the difference between correlated and non-correlated?

Question

Accepted Answer

A subquery is a query nested inside another query. Used in WHERE , SELECT , FROM , or HAVING clauses. Non-correlated — the inner query runs once , independently of the outer query. Returns a value or set used by the outer query. Correlated — the inner query references columns from the outer query ; runs once per row of the outer query. Slower; often replaceable with a JOIN. Optimizer can flatten — modern MySQL often rewrites correlated subqueries into joins automatically. EXISTS / NOT EXISTS — the most common correlated subquery form. Non-correlated = runs once (fast). Correlated = runs per row (slower). EXISTS vs IN: EXISTS stops at first match (faster for large sets), IN loads all results. Derived tables (subquery in FROM) must have an alias. 'Find employees earning above their department average' is the classic correlated subquery question.