SQL WITH Clause (Subquery Factoring)

‘WITH queryname’ clause is introduced in the oracle 9i release 2.This allows you to assign a name to subquery block and this name can be referenced in multiple places in the main query. Oracle treats this query name as inline view of as a table. You can even given alias to this query name. Actually WITH is introduced as replacement to normal subquery and this technique is called subquery factoring.

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

A WITH clause is really best used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

Restrictions on Subquery Factoring:

·         You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.

·         In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause.

Single Alias Code:

WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM <alias>;

i.e.

WITH q AS (SELECT dummy FROM dual)
SELECT dummy FROM q;

Multiple Alias Code:

WITH <alias_one> AS
  (subquery_sql_statement),
     <alias_two> AS
  (sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;

i.e.

WITH qb1 AS
  (SELECT inst_id FROM gv$session),
      qb2 AS
  (SELECT unique inst_id FROM qb1
   UNION ALL
   SELECT unique inst_id FROM qb1)
SELECT /*+ MATERIALIZE */ *
FROM qb1, qb2
WHERE qb1.inst_id = qb2.inst_id;