Difference between inline view and WITH clause

ctederived-tablesoracle

Inline views allow you to select from a subquery as if it were a different table:

SELECT
    *
FROM /* Selecting from a query instead of table */
    (
        SELECT
            c1
        FROM
            t1
        WHERE
            c1 > 0
    ) a
WHERE
    a.c1 < 50;

I've seen this referred to using different terms: inline views, WITH clause, CTE and derived tables. To me it seems they are different vendor specific syntax for the same thing.

Is this a wrong assumption? Are there any technical/performance differences between these?

Best Answer

There are some important differences between inline views (derived tables) and WITH clause(CTE) in Oracle. Some of them are quite universal, i.e. are applicable to other RDBMS.

  1. WITH can be used to build recursive subqueries, inline view -not (as far as I know the same is for all RDBMS that support CTE)
  2. Subquery in WITH clause is more likely be physically executed first ; in many cases, choosing between WITH and inline view makes optimizer to choose different execution plans (I guess it's vendor specific, maybe even version specific ).
  3. Subquery in WITH can be materialized as a temporary table ( I'm not aware if any other vendor but Oracle supports this feature).
  4. Subquery in WITH can be referenced multiple times , in other subqueries, and in the main query (true for most RDBMS).
Related Question