Understanding SQL Subqueries: Correlated vs Non-Correlated With Examples

Say you are writing a query to pull every customer whose lifetime order total sits above the average across your whole customer base. You write the outer SELECT, then you need a second calculation that depends on the full set of rows, and you realize you can’t express that with a plain WHERE clause on a single column. That is the moment subqueries enter the picture — a query nested inside another query, used to feed a value or a set of values into the surrounding statement.

Two flavors exist, and the difference between them determines whether your query finishes in under a second or grinds through millions of row-by-row lookups. A non-correlated subquery runs once, produces a result, and hands it to the outer query. A correlated subquery is re-evaluated for every row the outer query touches. That single distinction is what most tutorials gloss over, and it is the reason some subqueries feel instant while others time out.

The Scenario: Auditing Support Ticket Response Times

Suppose you maintain a support_tickets table for a SaaS product. Each row records a ticket, the agent who handled it, when it was opened, when it was first responded to, and the priority level. Your manager asks for a report: show every ticket whose first-response time was slower than the average first-response time for that ticket’s priority level. High-priority tickets should be compared against the high-priority average; low-priority tickets against the low-priority average.

That phrasing — “for that ticket’s priority level” — is the tell. The comparison value changes depending on which row you are looking at. A single global average won’t work. You need something that recalculates per row, and this is where the correlated/non-correlated decision has real consequences.

Here is a reasonable starting schema:

CREATE TABLE support_tickets (
    ticket_id       INT PRIMARY KEY,
    agent_id        INT,
    priority        TEXT,           -- 'high', 'medium', 'low'
    opened_at       TIMESTAMP,
    first_response_at TIMESTAMP     -- NULL if never answered
);

For this walkthrough, first_response_at being NULL means the ticket is still waiting, which matters later because NULLs silently drop out of aggregate comparisons.

Starting Simple: A Non-Correlated Subquery

The cleanest starting point is answering a related but simpler question first: which tickets were slower than the overall average response time across every ticket? No priority breakdown, just one global threshold.

SELECT
    ticket_id,
    priority,
    first_response_at - opened_at AS response_time
FROM support_tickets
WHERE first_response_at - opened_at > (
    SELECT AVG(first_response_at - opened_at)
    FROM support_tickets
    WHERE first_response_at IS NOT NULL
);

The inner query — the parenthesized SELECT AVG(...) — has no reference to the outer query at all. It stands alone. You could copy it out, run it by itself, and get a single number back: the average response time. That independence is the defining trait of a non-correlated subquery. The database engine is free to evaluate it exactly once, cache the resulting scalar value, and then scan the outer table comparing each row against that fixed number.

This is why non-correlated subqueries are usually cheap. One pass to compute the inner result, one pass over the outer table, done. On a table with ten million rows, you still pay for two scans, not ten million inner evaluations.

Non-correlated subqueries show up in three common shapes:

Scalar subqueries, returning a single value, as in the example above. Used with =, >, <, and similar comparison operators.
IN subqueries, returning a set, like WHERE agent_id IN (SELECT agent_id FROM agents WHERE region = 'EMEA').
FROM subqueries, where the inner query acts as a temporary table you select from, often called a derived table.

All three evaluate independently of the outer query. That independence is the property to look for when you are deciding whether an optimization technique applies.

The Problem: Per-Group Averages Need Per-Row Comparison

Now back to the actual request. Each ticket needs to be compared against the average for its own priority. Try to write it the same way as before:

SELECT
    ticket_id,
    priority,
    first_response_at - opened_at AS response_time
FROM support_tickets
WHERE first_response_at - opened_at > (
    SELECT AVG(first_response_at - opened_at)
    FROM support_tickets
    WHERE priority = ???   -- what goes here?
);

The inner query needs to know which priority to average over, but that priority belongs to the current outer row, not to the inner query alone. There is no fixed value to plug in. If you try to hardcode WHERE priority = 'high', you only solve the problem for high-priority tickets and you break every other row.

This is the exact gap that correlated subqueries fill. The inner query is allowed to reference a column from the outer query, and the engine runs the inner query once per outer row, substituting that row’s value. In SQL, you simply reference the outer table’s column directly inside the inner query, usually via a table alias to keep things readable:

SELECT
    t.ticket_id,
    t.priority,
    t.first_response_at - t.opened_at AS response_time
FROM support_tickets AS t
WHERE t.first_response_at - t.opened_at > (
    SELECT AVG(t2.first_response_at - t2.opened_at)
    FROM support_tickets AS t2
    WHERE t2.priority = t.priority
      AND t2.first_response_at IS NOT NULL
);

The key line is WHERE t2.priority = t.priority. The right side, t.priority, belongs to the outer row currently being evaluated. For a high-priority ticket, the inner query averages only high-priority tickets. For a low-priority ticket, it averages only low-priority tickets. The subquery is “correlated” because it cannot be run in isolation — it depends on a value that the outer query supplies one row at a time.

This syntax produces the correct answer. It also hides a cost that does not show up when you are reading the query, only when you run it against real data.

The Cost of Correlation, and When It Bites

Walk through what the engine does for the correlated version. For each of the N rows in support_tickets, it executes the inner aggregate query: scan the table, filter to matching priority, compute the average. If there is no index on priority, each of those inner scans touches the full table. Total work is roughly N inner scans of N rows each, which grows quadratically — O(N²).

To see this clearly, imagine the table holds 5 million tickets. The non-correlated global-average query scans the table twice: about 10 million row reads. The naive correlated version, without an index, performs 5 million inner scans of 5 million rows, which is 25 trillion row reads. That number is not a typo. It is why engineers describe correlated subqueries as “fine on small tables, catastrophic on large ones.”

An index on priority changes the picture substantially. With a B-tree index, the inner query for each row becomes an index range scan returning only the rows of that priority, typically a few percent of the table. Even so, a support team with three priorities on 5 million tickets means each inner scan still touches over a million rows, repeated 5 million times. The index helps, but the fundamental per-row reevaluation remains.

Verify the difference yourself with EXPLAIN. In PostgreSQL:

EXPLAIN ANALYZE
SELECT t.ticket_id, t.priority, t.first_response_at - t.opened_at
FROM support_tickets AS t
WHERE t.first_response_at - t.opened_at > (
    SELECT AVG(t2.first_response_at - t2.opened_at)
    FROM support_tickets AS t2
    WHERE t2.priority = t.priority
);

The output labels the inner plan a “SubPlan” and reports how many times it executed. If the number of executions equals the outer row count, you have confirmed the per-row behavior. Compare that execution count against the same report written with the window-function or grouped-join approach below, and the difference is usually two or three orders of magnitude in runtime on multi-million-row tables.

Rewriting the Same Query Without Correlation

The same report can be produced with a single aggregation. Compute the per-priority averages once, join them back to the ticket rows, and filter. This avoids any per-row reevaluation:

WITH priority_avg AS (
    SELECT
        priority,
        AVG(first_response_at - opened_at) AS avg_response
    FROM support_tickets
    WHERE first_response_at IS NOT NULL
    GROUP BY priority
)
SELECT
    t.ticket_id,
    t.priority,
    t.first_response_at - t.opened_at AS response_time
FROM support_tickets AS t
JOIN priority_avg AS p
  ON p.priority = t.priority
WHERE t.first_response_at - t.opened_at > p.avg_response;

The CTE runs once, producing three rows (one per priority). The join then matches each ticket to its priority’s average, a single hash join pass. Work is linear — O(N) — instead of quadratic. On the 5-million-row example, this version typically runs in a few seconds where the correlated version may take minutes or fail to complete at all.

The same result can be reached with a window function:

SELECT ticket_id, priority, response_time
FROM (
    SELECT
        ticket_id,
        priority,
        first_response_at - opened_at AS response_time,
        AVG(first_response_at - opened_at)
            OVER (PARTITION BY priority) AS avg_response
    FROM support_tickets
    WHERE first_response_at IS NOT NULL
) AS ranked
WHERE response_time > avg_response;

Both rewrites exploit the same insight: the per-priority average depends only on the priority, and there are only a handful of distinct priorities. Anything that depends on a small set of distinct values can be computed once and reused, which is precisely what a correlated subquery refuses to do.

When Correlated Subqueries Are the Right Tool

The performance story is one-sided, but the correctness story is not. There are questions that are hard to express without correlation, because the inner query’s logic depends on the outer row in a way that cannot be collapsed into a group-by.

A common example: find every ticket that was the first response for its assigned agent — the earliest first_response_at for that agent:

SELECT
    t1.ticket_id, t1.agent_id, t1.first_response_at
FROM support_tickets AS t1
WHERE t1.first_response_at = (
    SELECT MIN(t2.first_response_at)
    FROM support_tickets AS t2
    WHERE t2.agent_id = t1.agent_id
);

You can rewrite this with ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY first_response_at) = 1, and often should. But when the correlation compares a row to its neighbors across a chain — for example, “find tickets where this agent was idle for more than 24 hours after the previous response” — a straightforward correlated form can be clearer than the equivalent window rewrite, especially for someone reading the query without deep window-function fluency.

Correlation also appears naturally inside EXISTS and NOT EXISTS clauses:

SELECT c.customer_id, c.name
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.customer_id
);

This returns customers who have never placed an order — the classic “anti-join” question. Many query planners translate NOT EXISTS into an efficient anti-join operation regardless of syntax, so the correlated appearance does not always mean per-row execution. The important point is that the correlated keyword is a description of the query’s dependency structure, not a guarantee about the runtime. The planner decides how to execute.

EXISTS in particular has a useful property: it stops at the first matching row. For a query asking “does any row satisfy this condition,” EXISTS can be substantially faster than IN with a subquery, because IN may need to build the entire set before comparing. This is one of the few cases where a correlated-looking form often wins on raw speed, not just clarity.

How to Verify Which Version You Are Running

The habit that prevents surprise blowups is to read the execution plan before shipping a correlated query against a large table.

Run EXPLAIN (or EXPLAIN ANALYZE) on the query. Look for a SubPlan node or the SQL Server equivalent, a nested loop with a “correlated” annotation.
Check the execution count reported for that node. If it equals the outer row count, you are paying per-row cost.
Compare against the equivalent grouped or windowed rewrite. If the rewrite produces a drastically smaller cost estimate, prefer it.

Also worth checking: does the correlated subquery have a matching index on the correlated column? Without one, the inner query often degrades to a full table scan per outer row. With an index on priority or agent_id, the inner scans become range scans and the penalty shrinks, though it never disappears.

The Trade-Off in One Line

A non-correlated subquery is evaluated once and then applied; a correlated subquery is evaluated once per outer row. Use non-correlated when the inner result does not depend on the outer row at all — it is nearly always faster and often clearer. Reach for correlated syntax when the question requires a per-row dependency that cannot be expressed as a group, and either ensure the correlated column is indexed or rewrite as a window function or join before running it against a large table.

The rule of thumb that holds up in practice: if the inner query can be run standalone and produce a value you can write down, it is non-correlated and should stay that way. If it references a column that only makes sense in the context of the outer row, it is correlated, and the next question is whether the same logic can be expressed more cheaply.

What report are you trying to build — is the inner value a fixed threshold, or does it shift based on the outer row’s values? Describe the two tables and the question you need answered, and it becomes much easier to say which form fits and whether a rewrite will save significant time on your data.