Inside a subquery, which columns from enclosing queries are visible, and is the reverse allowed?

middleshould knowfreq 32%

answer

each FROM clause defines a scope
lookup goes inward first, then outward
more than one level out is allowed
the outer query only sees the result value
two copies of a table need two aliases

basics

~20 s

A subquery can reference range variables from any enclosing query block, not just the immediate parent. Visibility is one-way: the outer query sees only the subquery's returned value, never the tables or aliases used inside it.

solid answer

~50 s

Each query block contributes the aliases in its own FROM clause to a scope, and an inner block can see its own scope plus every enclosing one — so a subquery nested two levels deep may legally reference a column of the outermost query. Resolution runs innermost-first and walks outward, which is why qualifying names matters. The reverse never works: in … FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id), the alias o is invisible to the outer query, which only sees whether the subquery produced rows. A derived table in FROM is different — its mandatory alias exposes its output columns to the rest of the query, but its internal aliases are still hidden. And when the same table appears at both levels you must alias both copies, or the correlation cannot be written at all.

Each query block contributes the aliases in its own `FROM` clause to a scope, and an inner block can see its own scope plus every enclosing one — so a subquery nested two levels deep may legally reference a column of the outermost query. Resolution runs innermost-first and walks outward, which is why qualifying names matters. The reverse never works: in `… FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)`, the alias `o` is invisible to the outer query, which only sees whether the subquery produced rows. A derived table in `FROM` is different — its mandatory alias exposes its *output* columns to the rest of the query, but its internal aliases are still hidden. And when the same table appears at both levels you must alias both copies, or the correlation cannot be written at all.

code

sql · 9 lines

sql

SELECT c.id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND EXISTS (SELECT 1 FROM order_items i
                WHERE i.order_id = o.order_id
                  AND i.price > c.credit_limit)   -- c is two levels out
);

go deeper

for a junior

Remember the one-way rule: the inside of a subquery can look out, the outside cannot look in. Knowing that you must alias a table twice to correlate it to itself is the practical takeaway.

for a middle

State the innermost-first, then outward resolution rule, and show that a derived table's output columns are visible outside while its internal aliases are not. Expect to write a self-correlated aggregate with correct aliases.

for a senior

Judge the readability limit: multi-level correlation is legal but usually signals a query that should be restructured. Explain why moving a correlated subquery into a plain CTE does not preserve the correlation.

for a principal

Own the convention that keeps a large SQL codebase readable — aliasing and qualification rules, a ceiling on nesting depth, and a preferred shape for per-row derived values so scope questions never have to be re-litigated in review.

Scopes and range variables

Every query block — the outer SELECT, each subquery — introduces a scope holding the range variables declared in its own FROM clause. FROM customers c puts c in scope for that block. A column reference is resolved by looking for the name in the innermost scope, then in each enclosing scope in turn, outward.

Two consequences follow, and they are what interviewers probe.

Inward visibility: as far out as you like

An inner block can reference range variables from any enclosing block, not only its immediate parent. This is legal:

sql

SELECT c.id
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
    AND EXISTS (SELECT 1 FROM order_items i
                WHERE i.order_id = o.order_id
                  AND i.price > c.credit_limit)   -- reaches two levels out
);

The innermost block correlates to both o (one level out) and c (two levels out). It is valid, and it is also the point at which readability collapses: a reader now has to hold three scopes in their head to know what c.credit_limit means. Deep multi-level correlation is usually a sign the query wants restructuring, not a technique to reach for.

Outward visibility: none

A subquery's internals are private. In WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id), the outer query cannot mention o.amount anywhere — not in its SELECT list, not in its ORDER BY. All that crosses the boundary is what the subquery evaluates to: a truth value for EXISTS, a single value for a scalar subquery, a set of values for IN.

The tempting wrong move is adding orders to the outer FROM so the column becomes available. That does not expose the subquery's o — it creates a separate range variable over the same table and turns the query into a join, with join semantics (row multiplication) the EXISTS form was avoiding.

A derived table is the exception that proves the rule:

sql

SELECT c.id, t.total
FROM customers c
JOIN (SELECT o.customer_id, SUM(o.amount) AS total
      FROM orders o GROUP BY o.customer_id) t
  ON t.customer_id = c.id;

Here t is a FROM item, so its output columns (customer_id, total) are visible outside. Its internal alias o still is not — t.amount does not exist.

Aliasing the same table twice

When the inner query reads the same table as the outer one, aliases are what make correlation expressible:

sql

SELECT e.emp_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary)
                  FROM employees e2
                  WHERE e2.dept_id = e.dept_id);

Without e2, an unqualified salary inside would resolve to the inner employees and dept_id = dept_id would be a tautology comparing the inner row to itself, silently averaging the whole table. Two distinct aliases give you two independent row bindings — the outer row being tested and the inner rows being aggregated.

The direction rule that surprises people

A WITH query (a common table expression) at the top of a statement is not correlated and cannot be. It is defined before the main query has any rows, so it has no outer row to reference; there is no enclosing scope for it to see. That is why "just move the correlated subquery into a CTE to tidy it up" does not work mechanically: the correlation has to become a join condition or the CTE has to be joined back on the key.

Similarly, a subquery in the FROM clause cannot ordinarily reference columns of another FROM item at the same level — that requires the LATERAL keyword, which exists precisely to extend visibility sideways within a FROM clause.

Practical reading habits

Every table gets an alias; every column gets qualified. Then "which scope does this name come from" is answerable by eye rather than by consulting the schema.
Cover the outer query with your hand: if the inner block still resolves, it is uncorrelated. If it does not, note exactly which outer aliases it needs.
If an inner block reaches more than one level out, ask whether a join or a pre-aggregated derived table would say the same thing more plainly.

Common mistakes

Trying to select a subquery's internal columns in the outer query.
Believing correlation can only reach the immediately enclosing block.
Expecting a WITH query to see the main query's current row.
Omitting the second alias in a self-correlated aggregate and producing a whole-table average.

## Scopes and range variables Every query block — the outer `SELECT`, each subquery — introduces a scope holding the range variables declared in its own `FROM` clause. `FROM customers c` puts `c` in scope for that block. A column reference is resolved by looking for the name in the innermost scope, then in each enclosing scope in turn, outward. Two consequences follow, and they are what interviewers probe. ## Inward visibility: as far out as you like An inner block can reference range variables from **any** enclosing block, not only its immediate parent. This is legal: ```sql SELECT c.id FROM customers c WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id AND EXISTS (SELECT 1 FROM order_items i WHERE i.order_id = o.order_id AND i.price > c.credit_limit) -- reaches two levels out ); ``` The innermost block correlates to both `o` (one level out) and `c` (two levels out). It is valid, and it is also the point at which readability collapses: a reader now has to hold three scopes in their head to know what `c.credit_limit` means. Deep multi-level correlation is usually a sign the query wants restructuring, not a technique to reach for. ## Outward visibility: none A subquery's internals are private. In `WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)`, the outer query cannot mention `o.amount` anywhere — not in its `SELECT` list, not in its `ORDER BY`. All that crosses the boundary is what the subquery *evaluates to*: a truth value for `EXISTS`, a single value for a scalar subquery, a set of values for `IN`. The tempting wrong move is adding `orders` to the outer `FROM` so the column becomes available. That does not expose the subquery's `o` — it creates a *separate* range variable over the same table and turns the query into a join, with join semantics (row multiplication) the `EXISTS` form was avoiding. A derived table is the exception that proves the rule: ```sql SELECT c.id, t.total FROM customers c JOIN (SELECT o.customer_id, SUM(o.amount) AS total FROM orders o GROUP BY o.customer_id) t ON t.customer_id = c.id; ``` Here `t` is a `FROM` item, so its **output** columns (`customer_id`, `total`) are visible outside. Its internal alias `o` still is not — `t.amount` does not exist. ## Aliasing the same table twice When the inner query reads the same table as the outer one, aliases are what make correlation expressible: ```sql SELECT e.emp_id, e.salary FROM employees e WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id); ``` Without `e2`, an unqualified `salary` inside would resolve to the inner `employees` and `dept_id = dept_id` would be a tautology comparing the inner row to itself, silently averaging the whole table. Two distinct aliases give you two independent row bindings — the outer row being tested and the inner rows being aggregated. ## The direction rule that surprises people A `WITH` query (a common table expression) at the top of a statement is **not** correlated and cannot be. It is defined before the main query has any rows, so it has no outer row to reference; there is no enclosing scope for it to see. That is why "just move the correlated subquery into a CTE to tidy it up" does not work mechanically: the correlation has to become a join condition or the CTE has to be joined back on the key. Similarly, a subquery in the `FROM` clause cannot ordinarily reference columns of another `FROM` item at the same level — that requires the `LATERAL` keyword, which exists precisely to extend visibility sideways within a `FROM` clause. ## Practical reading habits - Every table gets an alias; every column gets qualified. Then "which scope does this name come from" is answerable by eye rather than by consulting the schema. - Cover the outer query with your hand: if the inner block still resolves, it is uncorrelated. If it does not, note exactly which outer aliases it needs. - If an inner block reaches more than one level out, ask whether a join or a pre-aggregated derived table would say the same thing more plainly. ## Common mistakes - Trying to select a subquery's internal columns in the outer query. - Believing correlation can only reach the immediately enclosing block. - Expecting a `WITH` query to see the main query's current row. - Omitting the second alias in a self-correlated aggregate and producing a whole-table average.

Why can't a WITH query reference a column from the main query?
A CTE is defined independently of the statement that uses it, so at the time it is evaluated there is no outer row to bind to — it has no enclosing scope. Correlation must therefore be expressed as a join condition when the CTE is referenced, or by a `LATERAL`-style construct rather than by the `WITH` clause itself.
If the outer query needs a value the subquery computed, how do you expose it?
Turn the subquery into a `FROM` item — a derived table with an alias — so its output columns become part of the outer scope, or return it as a scalar subquery in the SELECT list. Adding the same base table to the outer FROM instead creates a separate range variable and changes the query into a join.
What happens if you omit the second alias in a self-correlated aggregate?
The unqualified inner columns resolve to the inner copy of the table, so a condition like `dept_id = dept_id` compares a row to itself and is always true. The subquery then averages the entire table instead of the current row's department, and no error is raised.

saying these in an interview costs you the question

Thinks a subquery may only reference its immediate parent block
Tries to select a subquery's internal alias in the outer query
Expects a WITH query to see the outer row
Adds the base table to the outer FROM to 'expose' the alias
Omits a second alias when correlating a table to itself

Inside a subquery, which columns from enclosing queries are visible, and is the reverse allowed?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

Scopes and range variables

Inward visibility: as far out as you like

Outward visibility: none

Aliasing the same table twice

The direction rule that surprises people

Practical reading habits

Common mistakes

▾common follow-ups3 questions

▾red flags5 flags

§ context

▾related questions6 questions

▾related topics4 topics

Scopes and range variables

Inward visibility: as far out as you like

Outward visibility: none

Aliasing the same table twice

The direction rule that surprises people

Practical reading habits

Common mistakes

answer

go deeper

context