SQL Subqueries Explained: Correlated vs. Non-Correlated

What Is a Subquery?

A subquery is a query nested inside another query — used to compute an intermediate result the outer query then filters on, joins against, or selects directly. Subqueries are one of the first "compositional" SQL concepts beginners hit, and they're foundational to nearly everything more advanced: CTEs are essentially named, reusable subqueries, and many JOIN-based queries can be rewritten as subqueries and vice versa.

The Sample Data

-- employees
| id | name    | department  | salary | manager_id |
|----|---------|-------------|--------|------------|
| 1  | Alice   | Engineering | 120000 | NULL       |
| 2  | Bob     | Engineering | 95000  | 1          |
| 3  | Charlie | Marketing   | 80000  | NULL       |
| 4  | Diana   | Marketing   | 75000  | 3          |

Where Subqueries Can Appear

Subqueries aren't a single feature — they can be placed in four different positions, each with a different purpose:

• WHERE clause — filters rows based on a computed value or set
• FROM clause (a "derived table") — treats the subquery's result as if it were a table to select from or join against
• SELECT clause (a scalar subquery) — computes a single value to include as a column
• HAVING clause — filters aggregated groups based on a computed value

-- Subquery in WHERE: employees earning above the company average
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in FROM (derived table): pre-aggregate, then filter the aggregate
SELECT * FROM (
  SELECT department, AVG(salary) AS avg_sal FROM employees GROUP BY department
) dept_avgs
WHERE avg_sal > 90000;

-- Subquery in SELECT (scalar): attach a company-wide stat to every row
SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg
FROM employees;

A scalar subquery (in SELECT or a single-value comparison in WHERE) must return exactly one row and one column — if it can return more, the database raises a runtime error, not a warning.

Non-Correlated Subqueries

A non-correlated (or "simple") subquery is self-contained — it doesn't reference anything from the outer query, so it executes exactly once and its result is reused for every row the outer query evaluates.

SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- The inner AVG(salary) runs once; every outer row is compared against that single number

Correlated Subqueries

A correlated subquery references a column from the outer query, which means the database must conceptually re-run it once for every row the outer query processes — the inner and outer queries are "correlated" through that shared reference.

-- Correlated: employees earning more than their OWN department's average
SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary) FROM employees e2 WHERE e2.department = e.department
);

Notice e2.department = e.department — the inner query reaches out to e, a table defined in the outer query. That reference is what makes it correlated, and it's exactly why the subquery logically re-executes per outer row rather than once overall.

Correlated subqueries are also the standard way to write EXISTS / NOT EXISTS checks:

-- Departments that have at least one employee earning over 100k
SELECT DISTINCT department FROM employees e
WHERE EXISTS (
  SELECT 1 FROM employees e2 WHERE e2.department = e.department AND e2.salary > 100000
);

Subquery vs. JOIN: When to Use Which

Many subqueries can be rewritten as JOINs and vice versa — the choice usually comes down to readability and what the query needs to return.

-- Subquery version: returns only employee columns
SELECT name FROM employees
WHERE department IN (SELECT department FROM departments WHERE budget > 1000000);

-- JOIN version: needed if you also want columns from departments
SELECT e.name, d.budget
FROM employees e
JOIN departments d ON e.department = d.name
WHERE d.budget > 1000000;

General guidance: reach for a subquery (especially EXISTS) when you only need to filter based on another table without pulling its columns into the result. Reach for a JOIN when you need actual columns from both tables together, or when the relationship is one-to-many and you want every matching combination.

Subquery vs. EXISTS vs. IN — Performance and Correctness

• IN — compares against a list of values; can behave unexpectedly if the subquery's result contains a NULL (a NOT IN against a list containing any NULL returns zero rows for every comparison, a classic hidden bug)
• EXISTS — checks only for row existence and can short-circuit on the first match; unaffected by NULLs in the subquery's other columns, and generally the safer default for existence checks on large tables
• JOIN + DISTINCT — works but can be slower and easier to get wrong (forgetting DISTINCT after a one-to-many join silently duplicates rows)

-- The NOT IN + NULL trap: this can silently return ZERO rows
-- if even one row in the subquery has a NULL department
SELECT name FROM employees
WHERE department NOT IN (SELECT department FROM archived_departments);

-- Safer: NOT EXISTS is immune to this NULL trap
SELECT e.name FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM archived_departments a WHERE a.department = e.department
);

Performance Considerations

• Non-correlated subqueries run once — cheap regardless of outer row count.
• Correlated subqueries conceptually re-run per outer row; modern optimizers often rewrite them internally into a join or semi-join for efficiency, but this isn't guaranteed on every engine — check the execution plan if a correlated subquery is slow on a large table.
• A subquery used as a derived table in FROM can sometimes prevent index usage if the optimizer can't "see through" it — CTEs and derived tables are usually optimized similarly on modern engines, but always verify with EXPLAIN rather than assuming.
• Prefer NOT EXISTS over NOT IN whenever the subquery's column can contain NULLs — it avoids the correctness trap above entirely, not just a performance concern.

Common Mistakes

• Using NOT IN against a column that can be NULL — silently returns no rows at all, one of the most common hidden SQL bugs in production code.
• Writing a scalar subquery that can return multiple rows — works fine in testing on clean data, then fails at runtime the first time the underlying data has more than one match.
• Using a correlated subquery when a window function would be simpler and faster — e.g. "salary above department average" (shown above) can also be written as a single-pass window function; see our window functions guide.
• Nesting subqueries too deeply, hurting readability — a CTE (WITH) almost always makes the same logic clearer once you're past one level of nesting.

Common Interview Questions

1. What's the difference between a correlated and non-correlated subquery? A correlated subquery references a column from the outer query and conceptually runs once per outer row; a non-correlated subquery is self-contained and runs once total.
2. Why might NOT IN return unexpected results? If the subquery's result set contains any NULL, every comparison against that list becomes unknown, and the query returns zero rows — walk through the NULL trap shown above.
3. When would you use EXISTS instead of a JOIN? When you only need to check for related rows without needing any of their columns in the result — EXISTS avoids the extra DISTINCT/GROUP BY a JOIN would otherwise require.
4. Can a subquery be used to update or delete rows? Yes — UPDATE ... WHERE id IN (SELECT ...) and DELETE ... WHERE EXISTS (...) are both standard patterns for row-level operations based on another table's data.

Frequently Asked Questions

Is a CTE just a subquery with a name?

Functionally very similar for a single-use case — a CTE is a named, temporary result set that can also be referenced multiple times in the same query and, unlike a plain subquery, supports recursion.

Do subqueries always run before the outer query?

Non-correlated subqueries typically do, conceptually, run first. Correlated subqueries are logically evaluated per outer row, though the actual physical execution plan the optimizer chooses may differ — always confirm with EXPLAIN rather than assuming based on the SQL's written order.

Practice Subqueries

Subqueries are the foundation for almost every advanced pattern on this site — CTEs, window function filtering, and the EXISTS-based patterns used throughout our other guides all build on this concept. Try it hands-on with our SQL practice questions, or apply it to a full case study in our case studies. For the related GROUP BY / HAVING / WHERE execution order, see our dedicated guide.