Inline Views
An inline view is basically a query inside another query, which makes it a subquery. However, an inline view is a subquery with a twist. It only exists inside of the FROM clause as a run-time result set.
As explained in the description of a basic query, the FROM clause is resolved first in a query. Tables in a FROM clause resolve through parsing the query, which means looking the table name up in the data dictionary (or catalog). An inline view is resolved slightly differently. The parsing process takes any nested query found in the FROM clause and parses them separately, like a subprocess. If it finds that all is in order in the nested query, it marks it to be run before any join activity.
An example of an inline view is shown below with ANSI SQL:92 syntax:
This is the standard way of writing an inline view and it is consistent across other ANSI SQL:92 compliant databases. However, ANSI SQL: 1999 introduces the WITH clause. You can find cross-database examples of both in this data fabrication posting.
The idea behind the WITH clause is like a materialized view. The benefit of a WITH clause is that you parse, fetch, and execute the query only once no matter how many times it is found in a DML statatement. This provides great efficiency to SQL statements.
You shouldn’t really find it as any more complex than an inline view. Here’s the same query as the prior example using a WITH clause:
Oracle™ has implemented the WITH clause in compliance with the ANSI SQL: 1999 standard. Oracle™ labels it a subquery factoring clause. Oracle’s implementation is clean and limits itself to the idea of a materialized view, which is consistent with the ANSI specification.
Microsoft™ SQL Server has also implemented the WITH clause in compliance with the same SQL specification but they’ve also provided an ability to perform recursive lookups. Microsoft labels it as a Common Table Expression (CTE), you can use this to mimic a hierarchical query, provided you use a separate WITH clause for each node in the depth of a search. You can find an example of doing a node search in this blog entry.
