How SQL evaluates regular (non-correlated) subqueries Conceptually, a regular subquery (not correlated) is evaluated and combined with the outer query by which processing approach?

Introduction / Context:
Subqueries are SELECT statements embedded inside another SQL statement. Understanding how a regular (non-correlated) subquery is evaluated helps you predict results, reason about NULL behavior, and rewrite queries for performance or clarity.

Given Data / Assumptions:

• We are discussing regular subqueries (the inner query does not reference columns from the outer query).
• The outer query may use the subquery result in predicates such as IN, NOT IN, EXISTS, ANY, ALL, or as a scalar value.
• The DBMS optimizer may choose alternative physical plans, but the logical evaluation model still holds.

Concept / Approach:

Conceptually, a regular subquery is evaluated first to obtain a result set (or a single scalar). That result is then “plugged into” the outer query expression. This is often described as processing by nesting. Although optimizers can flatten or transform subqueries into joins, the logical semantics remain: evaluate the inner request, then apply it.

Step-by-Step Solution:

Verification / Alternative check:

Explaining plans frequently show subquery computation (or its equivalent join after transformation). The logical reading in documentation consistently presents regular subqueries as values supplied to the outer query.

Why Other Options Are Wrong:

• A: “Top down” implies evaluating the outer query without the subquery result, which is not how semantics are defined.
• D: The nesting model is the correct logical explanation.
• E: Hash join is a possible physical method after transformation, not a requirement.

Common Pitfalls:

• Confusing correlated and non-correlated subqueries; correlated ones run per outer row conceptually.
• Assuming physical execution must match the logical nesting order exactly.

Final Answer:

By nesting (the DBMS computes the subquery result and then applies it in the outer query).