A correlated subquery is an inner query that uses a column from the outer query. Because of that, it cannot be computed once and reused. Conceptually it is re-evaluated for each outer row. A non-correlated subquery stands on its own and runs once.
Example: employees above their department average
-- Correlated: inner query refers to e.department_id SELECT e.name, e.salary FROM employees e WHERE e.salary > ( SELECT AVG(s.salary) FROM employees s WHERE s.department_id = e.department_id );
Read it as: for each employee, compute the average of their department, then compare. With 1 million employees, the naive reading is 1 million small aggregations.
Why it is not always slow
Modern optimizers often decorrelate it. They rewrite the query into a join against a grouped result (compute the average per department once, then join), which is what you would write by hand. Whether that happens depends on the engine and on how complicated the inner query is. Subqueries that return a scalar inside SELECT, or have LIMIT or non-equality correlation, are the ones that usually stay slow. Check with EXPLAIN instead of guessing.
Two rewrites that always work
-- Window function: one pass over the table
SELECT name, salary
FROM (
SELECT name, salary,
AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
FROM employees
) x
WHERE salary > dept_avg;
-- Join to a pre-aggregated CTE
WITH dept AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees GROUP BY department_id
)
SELECT e.name, e.salary
FROM employees e JOIN dept d USING (department_id)
WHERE e.salary > d.avg_salary;In an interview, give the correlated version first because it is the most direct translation of the question. Then say you would check the plan, and offer the window version as the one you trust on a large table.