Difference between inline view and WITH clause?

Question

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?

Answer 1

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).

Answer 2

Other answers cover the syntax differences pretty well so I won't go into that. Instead this answer will just cover performance in Oracle.

The Oracle optimizer may choose to materialize the results of a CTE into an internal temporary table. It uses a heuristic to do this instead of cost-based optimization. The heuristic is something like "Materialize the CTE if it isn't a trivial expression and the CTE is referenced more than once in the query". There are some queries for which the materialization will improve performance. There are some queries for which the materialization will dramatically degrade performance. The following example is a bit contrived but it illustrates the point well:

First create a table with a primary key that contains integers from 1 to 10000:

CREATE TABLE N_10000 (NUM_ID INTEGER NOT NULL, PRIMARY KEY (NUM_ID));

INSERT /*+APPEND */ INTO N_10000
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL <= 10000
ORDER BY LEVEL;

COMMIT;

Consider the following query that uses two derived tables:

SELECT t1.NUM_ID
FROM 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t1
LEFT OUTER JOIN 
(
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
) t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

We can look at this query and quickly determine that it won't return any rows. Oracle should be able to use the index to determine that as well. On my machine the query finishes nearly instantaneously with the following plan:

I don't like repeating myself, so let's try the same query with a CTE:

WITH N_10000_CTE AS (
  SELECT n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

Here is the plan:

That's a really bad plan. Instead of using the index, Oracle materializes 10000 X 10000 = 100000000 rows into a temp table only to eventually return 0 rows. The cost of this plan is around 6 M which is much higher than the other query. The query took 68 seconds to finish on my machine.

Note that the query could have failed if there isn't enough memory or free space in the temp tablespace.

I can use the undocumented INLINE hint to disallow the optimizer from materializing the CTE:

WITH N_10000_CTE AS (
  SELECT /*+ INLINE */ n1.NUM_ID
  FROM N_10000 n1
  CROSS JOIN N_10000 n2
)
SELECT t1.NUM_ID
FROM N_10000_CTE t1
LEFT JOIN N_10000_CTE t2 ON t1.NUM_ID = t2.NUM_ID
WHERE t1.NUM_ID <= 0;

That query is able to use the index and finishes almost instantly. The cost of the query is the same as before, 11. So for the second query, the heuristic used by Oracle resulted it in it picking a query with an estimated cost of 6 M instead of a query with an estimated cost of 11.

Answer 3

For SQL Server, WITH CTE specifies the temporary named result set, but is only required for the first CTE. i.e.

WITH CTE AS (SELECT .... FROM), 
CTE2 AS (SELECT .... FROM)

SELECT CTE.Column, CTE2.Column
FROM CTE
INNER JOIN CTE2 on CTE.Column = CTE2.Column

But this isn't a subquery, or correlated subquery. There are things you can do with a CTE what you can't do with a sub-query in SQL Server, like update the Tables referenced in a CTE. Here is an example of updating a table with a CTE.

A subquery would be something like

SELECT
   C1,
   (SELECT C2 FROM SomeTable) as C2
FROM Table

Or a correlated sub-query is what you have provided in your OP if you were to reference / join / limit your results based on a.c1.

So, they definitly aren't the same thing, though in a lot of cases you could use one or more of these methods to achieve the same result. It just depends on what that end result is.

Answer 4

The main difference between with clause and a subquery in Oracle is that you can reference a query within the clause multiple times. You can then do some optimizations with it like turning it into a temp table using materialize hint. You can also do recursive queries with it by referencing itself inside a with clause. You can't do that with an inline view.

More information can be found here and here.

Answer 5

You need to be careful with CTE's in SQL server not just oracle, there are cases where queries perform much worse when using CTE's compared to subqueries, cross apply, etc.

Like always it is important to test any query under various load conditions to determine which one works the best.

Similar to @scsimon with oracle, sometimes MS SQL server doesn't do what you expect in regards to index usage.

If you are going to use the same data more than once, CTE's can be more useful, if you are only using it once, often a subquery is faster in large datasets.

e.g. select * from (my subquery) join something else ...