Oracle Inline Views: Subqueries in the FROM Clause

A subquery placed in the FROM clause is commonly called an inline view in Oracle SQL. From the perspective of the containing query, the inline view supplies rows and columns in much the same way as a table or stored view. It exists only as part of the statement, however. It does not create a permanent schema object.

An inline view is useful when one query block needs to operate on a result produced by another query block. The inner query can join, filter, group, or calculate values. Its select list defines the columns exposed to the outer query. The outer query can then select, join, or filter those derived columns.

What is an inline view?

The general structure of an inline-view query is:

SELECT iv.derived_column,
       iv.other_column
FROM   (
         SELECT expression AS derived_column,
                other_column
         FROM   source_table
         WHERE  inner_condition
       ) iv
WHERE  iv.derived_column > threshold_value;

The parenthesized SELECT is one written query block. The alias iv gives its result a name within the outer query block. Oracle allows the outer query to refer only to columns projected by the inner select list. A source-table column that is not selected by the inline view is not visible to the outer query.

The term “virtual table” can help describe the logical result, but it should not be interpreted as guaranteed temporary storage. Oracle may retain the inline view as a separate operation, merge it into the outer query, or apply another valid transformation. The SQL defines the required result, while the optimizer determines how to produce that result.

Inline view versus stored schema view

An inline view is written directly inside one SQL statement. It has no independent name in the data dictionary and cannot be referenced by a later statement. Its table alias, such as sales_summary, exists only within the containing statement. This makes an inline view appropriate for an intermediate result that belongs to one query.

A stored view is created with CREATE VIEW, receives a schema-object name, and can be reused by other statements when privileges allow. Stored views can provide a reusable interface or security boundary, while inline views are convenient for local query organization. This difference concerns definition and reuse; it does not guarantee different physical execution. Oracle can consider view-merging transformations for either form when the transformation is valid.

Do not confuse an inline view with an object-relational nested-table collection. Accessing collection elements uses Oracle's TABLE collection-expression syntax and has its own rules. A later object-relational lesson can cover that feature without making it the reason for introducing ordinary FROM-clause subqueries here.

How query blocks define column scope

Each query block has its own select list, row sources, and conditions. Consider an inner query that calculates SUM(cs.total_sale_amount) AS dollars. Within that query block, dollars is a select-list alias. Once the query becomes an inline view named sales_summary, the alias becomes an exposed column of that row source. The outer query can therefore select sales_summary.dollars and use it in its own WHERE clause.

That scope boundary is important. A select-list alias is not generally a replacement for its expression everywhere in the same query block. In this lesson, the outer query can filter dollars because the calculation belongs to the completed inner query block and is exposed as an inline-view column. The outer condition is not attempting to use the alias in the inner query's own WHERE clause.

Parsing is not a fixed clause-execution sequence

The legacy version called this topic an “Oracle parsing sequence,” but parsing and query execution are different concepts. During parsing, Oracle checks SQL syntax and semantics, resolves referenced objects and columns, and checks whether reusable SQL is available. For a statement that requires a new plan, optimization and row-source generation follow before execution.

It is still useful to read an inline-view statement logically from the inside outward. The inner query defines a row set and exposes named columns; the outer query operates on those columns. This is a model for understanding meaning and scope, not a promise that Oracle must execute the entire inner query, store every row, and only then begin the outer query.

The optimizer can reorder operations and transform query blocks when doing so preserves the statement's result. The execution plan might show a separate VIEW operation, or the inline view might disappear from the plan because its query block was merged. Therefore, distinguish the written SQL structure from the physical row-source tree.

Summarize customer sales in an inline view

The Pet Store example asks for customers whose qualifying sales total is greater than 50. The inner query joins customers to sales, retains sale rows whose tax amount is greater than 1, and calculates a total for each customer. The outer query filters those calculated totals:

SELECT sales_summary.lastname,
       sales_summary.dollars
FROM   (
         SELECT c.cust_id,
                c.lastname,
                SUM(cs.total_sale_amount) AS dollars
         FROM   customer c
         JOIN   customer_sale cs
           ON   cs.cust_id = c.cust_id
         WHERE  cs.tax_amount > 1
         GROUP  BY c.cust_id,
                   c.lastname
       ) sales_summary
WHERE  sales_summary.dollars > 50
ORDER  BY sales_summary.lastname;

The query can be interpreted logically in these steps:

1. The inner query joins each sale to its customer through cust_id.
2. The inner WHERE condition keeps sale rows whose tax_amount is greater than 1.
3. The inner GROUP BY forms one group for each customer identifier and last name.
4. SUM calculates the qualifying sales total and exposes it as dollars.
5. The inline view is named sales_summary.
6. The outer WHERE condition retains totals greater than 50.
7. The final ORDER BY produces a predictable display order.

If the lesson's source data is unchanged, the query returns:

Grouping by cust_id as well as lastname prevents two different customers who share a last name from being combined into one group. The outer select list does not need to display cust_id, but the identifier remains available within the inline view and makes the grouping rule accurate.

Use HAVING when one query block is sufficient

An inline view is not required for every aggregate filter. In this example, HAVING can filter the grouped total in the same query block:

SELECT c.lastname,
       SUM(cs.total_sale_amount) AS dollars
FROM   customer c
JOIN   customer_sale cs
  ON   cs.cust_id = c.cust_id
WHERE  cs.tax_amount > 1
GROUP  BY c.cust_id,
          c.lastname
HAVING SUM(cs.total_sale_amount) > 50
ORDER  BY c.lastname;

The WHERE clause filters individual sale rows before grouping, while HAVING filters the completed customer groups. This version is concise and clearly expresses the requirement. The inline-view version becomes more valuable when the derived result must be joined to other row sources, filtered through several outer conditions, or separated into a named conceptual step.

Neither form is inherently faster merely because of its syntax. Oracle can transform equivalent statements into similar physical plans. Choose a form that states the rule correctly and remains understandable, then inspect current execution evidence when performance matters.

Name the query block with a WITH clause

A common table expression, also called subquery factoring, can make the same query-block relationship easier to read. The factored subquery appears first and receives the descriptive name sales_summary:

WITH sales_summary AS (
  SELECT c.cust_id,
         c.lastname,
         SUM(cs.total_sale_amount) AS dollars
  FROM   customer c
  JOIN   customer_sale cs
    ON   cs.cust_id = c.cust_id
  WHERE  cs.tax_amount > 1
  GROUP  BY c.cust_id,
            c.lastname
)
SELECT sales_summary.lastname,
       sales_summary.dollars
FROM   sales_summary
WHERE  sales_summary.dollars > 50
ORDER  BY sales_summary.lastname;

The WITH clause names a query block for the duration of the statement. It can be especially helpful when the same result is referenced more than once or when a long inline view would obscure the outer query. Like an inline view, a factored subquery does not require materialization merely because of how it is written. Oracle can treat it as an inline view or as a temporary result according to the statement and optimizer decision.

View merging and predicate pushing

Oracle's optimizer represents an unmerged inline view as a separate query block. Through view merging, it may combine that block with the containing query block. Merging can expose additional join orders, access paths, and transformations. Some query constructs restrict simple view merging, while more complex merging may still be considered when it is valid and has a lower estimated cost.

When a view remains separate, Oracle may use predicate pushing. A relevant outer condition can be pushed into the view query block when that transformation is legal. Applying the condition closer to the underlying data may improve filtering or make an index usable. View merging and predicate pushing are possibilities, not instructions embedded in the SQL text.

The selected plan depends on the query structure, object definitions, indexes, statistics, data distribution, and optimizer environment. An inline view should therefore be used to express a correct query structure—not as an assumed optimization barrier and not as a guarantee that Oracle creates a temporary table.

When should you use an inline view?

An inline view is a good candidate when you need to:

• Filter a derived aggregate or analytic result from another query block.
• Join an aggregated result to a table, view, or second inline view.
• Give a complex intermediate row set a clear alias and scope boundary.
• Isolate grouping, DISTINCT, set operations, or row-limiting logic when that structure improves clarity.
• Expose calculated columns to a containing query.

Avoid adding an inline view when it merely wraps a simple statement without improving meaning. A direct WHERE or HAVING condition may be clearer. Consider a WITH clause when the intermediate result deserves a prominent name or is referenced repeatedly.

Inline-view summary

• A subquery in the FROM clause is an inline view.
• The inner select list defines the columns visible to the outer query.
• An inline-view alias identifies the derived row source within the containing query block.
• The written inner and outer query blocks explain logical scope, not a guaranteed physical execution order.
• Oracle may keep the view separate, merge it, or push predicates when a transformation is valid and cost-effective.
• HAVING may be simpler when the only goal is filtering a grouped result in one query block.
• A WITH clause offers another way to name and organize an intermediate query result.
• Use current execution plans and measurements rather than assuming materialization or a particular access path.

From Clause Query Exercise

Practice constructing an inline view in the From Clause Query - Exercise.

The next lesson concludes this module on joins and subqueries.