With As Select

Subquery factoring, also known as the WITH clause, provides a convenient and flexible way for us to define subqueries and in-line views in Oracle 9i. The primary purpose of subquery factoring is to reduce repeated table accesses by generating temporary datasets during query execution. However, even if we do not take advantage of this internal query optimisation, we can use subquery factoring to structure our complex SQL statements in a more logical and understandable format. In this article we will look at how we can benefit from this new syntax and related optimisation.
syntax overview

The syntax for subquery factoring is as follows.

WITH subquery_name AS (
SELECT ...
)
,    another_subquery_name AS (
SELECT ...
)
SELECT ...
FROM   subquery_name          sq1
,      another_subquery_name  sq2
WHERE  sq1... = sq2...;

As stated in the introduction to this article, subquery factoring is invoked via the new WITH clause. The WITH keyword is the first in the syntax diagram for SELECT, as we can see in the syntax example above. This example defines two subqueries and then joins them together in the “main body” of the SELECT statement. This is the semantic equivalent of the following pseudo-select.

SELECT ...
FROM  (SELECT ...) inline_view_name
,     (SELECT ...) another_inline_view_name
WHERE ...

The following syntax example is possibly more common in everyday use. We define two named subqueries, but the second builds on the first.

WITH subquery_name AS (
SELECT ...
FROM   table
)
,    another_subquery_name AS (
SELECT ...
FROM   subquery_name
)
SELECT ...
FROM   another_subquery_name
WHERE  ...

This form of subquery factoring is the semantic equivalent of the following pseudo-SQL.

SELECT ...
FROM  (SELECT ...
FROM  (
SELECT ...))

This is where subquery factoring is at its most useful. Using subqueries in this way helps to break down complex SQL statements and structure them more “procedurally” without having to rely on multiple levels of nested in-line views. This is particularly important when using analytic functions which often need at one least level of nesting. Oracle, meanwhile, manages the optimisation of such “procedural SQL” statements through its query merging techniques, such that what we define in our SQL is not necessarily what Oracle runs under the covers.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: