Oracle Correlated Subqueries and Outer References

A subquery is correlated when it contains a column reference to a table or alias defined by a parent statement. That outer reference makes the inner query's logical result depend on values from the current parent row. Without an outer reference, the subquery is uncorrelated and can be understood independently of each parent row.

Oracle AI Database 26ai permits correlated subqueries in parent SELECT, UPDATE, and DELETE statements. This lesson uses SELECT statements so that the relationship between query blocks is easy to see. The same principles of alias scope, result cardinality, and null behavior matter in data-modification statements.

A correlated statement has several roles:

• The parent statement or outer query block supplies a value to the nested query.
• The inner query block reads its own row source and uses the outer value.
• An outer reference is a qualified inner-block reference to an alias defined by a parent block.
• The correlation condition connects the inner rows to the current logical parent row.

The following generic form uses o for the outer alias and i for the inner alias. The reference to o.key_value appears inside the subquery even though alias o is defined by the containing query:

SELECT o.display_value
FROM   outer_table o
WHERE  o.measure_value > (
         SELECT MAX(i.measure_value)
         FROM   inner_table i
         WHERE  i.key_value = o.key_value
       );

The correlation condition is i.key_value = o.key_value. For each logical outer row, it restricts the inner aggregate to rows with the related key. Correlation describes this dependency between query blocks; it does not by itself guarantee that the inner query returns one row.

Consider a report comparing each product's update time with a care-log time. This uncorrelated subquery calculates one maximum datetime across the entire PET_CARE_LOG table:

SELECT p.product_name
FROM   product p
WHERE  p.last_update_date > (
         SELECT MAX(pcl.log_datetime)
         FROM   pet_care_log pcl
       )
ORDER  BY p.product_name;

The inner block contains no reference to alias p. Every product is compared with the same global maximum log datetime. That may be a valid question, but it does not ask about the latest log for each individual product.

The correlated version adds an outer reference:

SELECT p.product_name
FROM   product p
WHERE  p.last_update_date > (
         SELECT MAX(pcl.log_datetime)
         FROM   pet_care_log pcl
         WHERE  pcl.product_id = p.product_id
       )
ORDER  BY p.product_name;

Here, pcl.product_id belongs to the inner row source, while p.product_id comes from the parent query. The aggregate now finds the maximum care-log datetime for the current logical product. A single predicate changes the question from “after the latest log anywhere?” to “after the latest log for this product?”

The correlated Pet Store query can be read in five steps:

1. The outer query reads a product through alias p.
2. The inner query reads care-log rows through alias pcl.
3. The correlation condition keeps inner rows whose product ID matches the outer product ID.
4. MAX(pcl.log_datetime) produces the latest matching log datetime as a scalar value.
5. The outer condition retains the product when its update datetime is later than that value.

Product updated after its latest care-log entry in the Pet Store sample data

PRODUCT_NAME
------------
Puppy

The result table reflects the supplied Pet Store sample. The ORDER BY makes the presentation deterministic if more products qualify. The correlation condition, rather than the order of the SQL clauses, establishes the relationship between each product and its care logs.

To debug a correlated query, follow one outer row without assuming a physical execution plan. Suppose the logical outer row is product 42. Inside the subquery, p.product_id therefore supplies the value 42. The correlation condition keeps only care-log rows whose pcl.product_id is 42, and MAX reduces those matching datetimes to the most recent value.

The outer block then compares product 42's last_update_date with that scalar. If the product update is later, the row qualifies. If it is equal to or earlier than the latest log, the row does not qualify. If no log row matches product 42, the maximum is null and the comparison is unknown.

Repeat the same reasoning with another product ID and the inner matching set changes. That changing set is the practical meaning of correlation. By contrast, the uncorrelated global-maximum example uses the same complete care-log set for every product. This tracing technique helps distinguish an intentional outer reference from a filter that merely happens to use similarly named columns.

The subquery is used on the right side of >, so it must behave as a scalar expression. A scalar subquery returns one selected column and no more than one row. Its possible outcomes are:

• If it returns one row, the selected value becomes the scalar value.
• If it returns zero rows, the scalar value is NULL.
• If it returns more than one row, Oracle reports an error because one comparison value cannot be chosen.

Correlation does not enforce this rule. The MAX aggregate makes the care-log subquery produce one aggregate row. Without GROUP BY, MAX still produces one row when no inner rows match, but its value is null.

Use single-value operators such as =, >, <, >=, <=, or <> only when the subquery is scalar. Use IN, ANY, or ALL when several comparison values are intended. Use EXISTS when the question concerns whether a related row is present rather than the value that row supplies.

A second example compares each employee's salary with a value calculated for that employee's department. The sample requires only the EMPLOYEES table; a separate departments table is not needed.

Employee sample data

EMPLOYEE_ID	NAME	SALARY	DEPARTMENT_ID
1	John	5000	10
2	Jane	6000	10
3	Mark	5500	20
4	Lucy	4500	20

SELECT e.employee_id,
       e.name,
       e.salary,
       e.department_id
FROM   employees e
WHERE  e.salary = (
         SELECT MAX(peer.salary)
         FROM   employees peer
         WHERE  peer.department_id = e.department_id
       )
ORDER  BY e.department_id,
          e.employee_id;

Alias e represents the candidate employee in the outer block. Alias peer represents employees considered by the inner block. For each logical candidate, the correlation condition limits the aggregate to peers in the same department. The equality retains candidates whose salary equals that departmental maximum.

Employees earning the maximum salary in their department

EMPLOYEE_ID	NAME	SALARY	DEPARTMENT_ID
2	Jane	6000	10
3	Mark	5500	20

The query returns every employee tied for the maximum. If two employees in department 10 both earn 6000, both satisfy the equality. It does not arbitrarily choose one employee. An employee whose department_id is null does not match peers through ordinary equality, because NULL = NULL is unknown.

Oracle resolves an unqualified column in a subquery by looking first at tables named in that subquery and then at tables in parent statements. Although this lookup is documented, instructional and production SQL should qualify correlated columns explicitly.

Qualification makes the source of each value visible during review. It also reduces the risk of accidental correlation. If a developer intends to reference an inner column but uses a name that exists only in the outer table, Oracle can bind that name to the parent block instead of reporting the mistake the developer expected. Role-based aliases such as peer communicate intent better than leaving scope implicit.

Conceptually, a correlated subquery is evaluated using values from each row processed by the parent statement. This model explains the SQL result, but it does not promise that Oracle physically starts and completes the inner query once for every outer row.

The optimizer may unnest an eligible subquery, transform it into a join, cache useful results, or choose another semantically equivalent plan. Aggregates, grouping, query nesting, and other features influence which transformations are available. A correlated statement is not inherently faster or slower than a join formulation; performance must be assessed with representative data, current statistics, and the observed plan.

The maximum-per-department rule can also be written by calculating departmental maxima first and joining them to employees:

SELECT e.employee_id,
       e.name,
       e.salary,
       e.department_id
FROM   employees e
JOIN   (
         SELECT department_id,
                MAX(salary) AS max_salary
         FROM   employees
         GROUP  BY department_id
       ) dept_max
       ON dept_max.department_id = e.department_id
      AND dept_max.max_salary = e.salary
ORDER  BY e.department_id,
          e.employee_id;

For the stated rule and non-null department matches, this formulation produces the same sample result and preserves salary ties. It is an alternative expression of the relationship, not a universal performance improvement. The optimizer may also transform a nested statement without the developer manually rewriting it.

Several small changes can alter the question or make the statement invalid:

• Omitting the outer reference: removing pcl.product_id = p.product_id changes a per-product maximum into one global maximum. The SQL may still run, but it answers a different question.
• Returning several rows to a scalar operator: selecting individual log datetimes on the right side of > can return more than one row. Use an aggregate only when the business rule asks for a value such as the maximum; otherwise choose the appropriate multirow condition.
• Leaving columns unqualified: name resolution can bind a reference to an outer column when no matching inner column exists. This may create accidental correlation instead of the expected error.
• Suppressing an error with an arbitrary row: adding ROWNUM = 1 merely to force scalar cardinality can select an unspecified qualifying row. Define which row the rule requires rather than hiding a multirow result.
• Rewriting as a join without preserving cardinality: joining directly to detail rows can duplicate outer rows. Pre-aggregate to one row per correlation key when the correlated scalar aggregate represents one grouped value.

Validate a rewrite with groups containing zero, one, and several inner rows, as well as tied aggregate values and null keys. A test set containing only one matching detail row per parent cannot expose cardinality or null-semantics mistakes.

• A correlated subquery contains an outer reference to an alias defined by a parent query block.
• The correlation condition restricts inner rows according to values from the current logical parent row.
• Correlation does not guarantee scalar cardinality; a scalar subquery must return one column and no more than one row.
• A scalar subquery with zero rows has a null value, while one returning several rows causes an error.
• A scalar aggregate such as MAX returns one aggregate row, with a null value when no input row matches.
• Maximum-per-group comparisons return every tied outer row.
• Qualified aliases expose scope and help prevent accidental correlation.
• The nested SQL describes logical dependence, while the optimizer selects the physical execution strategy.