What is a subquery and what is the difference between correlated and non-correlated?
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 subquery (runs once)
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- The AVG is calculated ONCE and used for all rows
-- Correlated subquery (runs per row)
SELECT name, salary, department
FROM employees e1
WHERE salary > (
SELECT AVG(salary) FROM employees e2
WHERE e2.department = e1.department -- References outer query!
);
-- AVG is recalculated for EACH employee's department
-- Subquery in SELECT
SELECT name,
(SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) AS order_count
FROM users;
-- Subquery in FROM (derived table)
SELECT dept, avg_sal
FROM (
SELECT department AS dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
) AS dept_stats
WHERE avg_sal > 60000;
-- EXISTS (correlated)
SELECT name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Non-correlated: the inner AVG runs once for the whole table. Correlated: the inner AVG references e1.department from the outer query — recalculated per row. SELECT subquery counts orders per user.
FROM subquery (derived table) creates a temporary result set. EXISTS checks if matching rows exist (efficient for 'has any' checks).
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.