Oracle With Clause - Common Table Expressions
Version 11.2.0.3

General Information
The WITH query_name clause lets you assign a name to a subquery block. You can then reference the subquery block multiple places in the query by specifying the query name. Oracle optimizes the query by treating the query name as either an inline view or as a temporary table.

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 most valuable when the result of the WITH query is required more than one time in the body of the main query such as where one averaged value needs to be compared against two or three times. The point is to minimize the number of accesses to a table joined multiple times into a single query.

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.
 
Basic
Single alias WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM <alias>;
WITH q AS (SELECT dummy FROM dual)
SELECT dummy FROM q;
Double alias

(corrected per notes from (Colin 't Hart and Roy Fraties)		 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>;
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;
 
Advanced
Recursive WITH Clause WITH <alias> AS
  (subquery_sql_statement)
SEARCH <BREADTH | DEPTH> FIRST BY <column_name> [ASC | DESC] [NULLS FIRST | NULLS LAST]
SET <ordering_column>
CYCLE (alias) SET <cycle_mark_alias> TO <cycle_value> DEFAULT <no_cycle_value>
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
WITH dup_hiredate (eid, emp_last, mgr_id, reportLevel, hire_date, job_id) AS (
  SELECT employee_id, last_name, manager_id, 0 reportLevel, hire_date, job_id
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id,
  r.reportLevel+1 reportLevel, e.hire_date, e.job_id
  FROM dup_hiredate r, employees e
  WHERE r.eid = e.manager_id)
SEARCH DEPTH FIRST BY hire_date SET order1
CYCLE hire_date SET is_cycle TO 'Y' DEFAULT 'N'
SELECT LPAD(' ',2*reportLevel)||emp_last emp_name, eid, mgr_id, hire_date, job_id, is_cycle
FROM dup_hiredate
ORDER BY order1;

WITH emp_count (eid, emp_last, mgr_id, mgrLevel, salary, cnt_employees) AS (
  SELECT employee_id, last_name, manager_id, 0 mgrLevel, salary, 0 cnt_employees
  FROM employees
  UNION ALL
  SELECT e.employee_id, e.last_name, e.manager_id, r.mgrLevel+1 mgrLevel, e.salary, 1
  cnt_employees
  FROM emp_count r, employees e
  WHERE e.employee_id = r.mgr_id)
SEARCH DEPTH FIRST BY emp_last SET order1
SELECT emp_last, eid, mgr_id, salary, SUM(cnt_employees), MAX(mgrLevel) mgrLevel
FROM emp_count
GROUP BY emp_last, eid, mgr_id, salary
HAVING MAX(mgrLevel) > 0
ORDER BY mgr_id NULLS FIRST, emp_last;
 
Demo
Mailing list code EXPLAIN PLAN FOR
SELECT per_h_email
FROM person p
WHERE p.per_ok2_email = 'A'
AND p.per_h_email IN (
  SELECT person_id
  FROM person
  WHERE per_db = 'Y')
UNION
SELECT po_w_email
FROM poie o
WHERE o.po_status = 'A'
AND o.po_w_email IN (
  SELECT person_id
  FROM person
  WHERE per_db = 'Y');

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
WITH w AS (
  SELECT person_id
  FROM person
  WHERE per_db = 'Y')
SELECT per_h_email
FROM person p, w
WHERE p.person_id = w.person_id
AND p.per_ok2_email = 'A'
AND p.per_h_email IS NOT NULL
UNION
SELECT po_w_email
FROM poie o, w
WHERE o.person_id = w.person_id
AND o.po_status = 'A'
AND o.po_w_email IS NOT NULL;

SELECT * FROM TABLE(dbms_xplan.display);
WITH with CONNECT BY

Built from code posted by Michel Cadot at comp.databases.oracle.misc 28-Sep-2006		 CREATE TABLE t1 (
pname VARCHAR2(10),
cases NUMBER(3));

INSERT INTO t1 VALUES ('Morgan',2);
INSERT INTO t1 VALUES ('Dan',3);
INSERT INTO t1 VALUES ('Jack',2);
INSERT INTO t1 VALUES ('Helen',1);
COMMIT;

SELECT * FROM t1;

CREATE TABLE t2 AS
SELECT pname FROM t1
WHERE 1=2;

SELECT * FROM t2;

INSERT INTO t2
WITH rn AS (
  SELECT rownum rn
  FROM dual
  CONNECT BY LEVEL <= (SELECT MAX(cases) FROM t1))
SELECT pname
FROM t1, rn
WHERE rn <= cases
ORDER BY pname;

SELECT * FROM t2;
Another WITH Clause with CONNECT BY from Michel Cadot VARIABLE liste VARCHAR2(100)

EXECUTE :liste := '5, 25, 41, 52';

WITH liste AS (
  SELECT SUBSTR(:liste, INSTR(','||:liste||',', ',', 1, rn),
  INSTR(','||:liste||',', ',', 1, rn+1) -
  INSTR(','||:liste||',', ',', 1, rn)-1) valeur
FROM (
  SELECT ROWNUM rn FROM dual
  CONNECT BY LEVEL<=LENGTH(:liste) - LENGTH(REPLACE(:liste,',',''))+1))
SELECT TRIM(valeur)
FROM liste;
Another WITH Clause demo based on code posted  by David Fitzjarrell at c.d.o.server 01-AUG-2006 CREATE TABLE test (
job_id   NUMBER(3),
batch_id NUMBER(3),
action   VARCHAR2(4),
actdate  DATE);

INSERT INTO test VALUES (1, 1, 'SENT', SYSDATE-5);
INSERT INTO test VALUES (2, 1, 'RECV', SYSDATE-4);
INSERT INTO test VALUES (3, 2, 'SENT', SYSDATE-3);
INSERT INTO test VALUES (4, 2, 'RECV', SYSDATE-2);
INSERT INTO test VALUES (5, 3, 'SENT', SYSDATE-1);

WITH col_generator AS (
SELECT t1.batch_id, DECODE(t1.action, 'SENT', t1.actdate) sent,
DECODE(t2.action,'RECV', t2.actdate) received
FROM test t1, test t2
WHERE t2.batch_id(+) = t1.batch_id)
SELECT batch_id, max(sent) sent, max(received) received
FROM col_generator
GROUP BY batch_id
ORDER BY 1;
Another WITH Clause demo posted by Maxim Demenko to
comp.databases.oracle.misc 3-Mar-2008.

This demo takes an input of numbers, in seconds and returns minutes:seconds.		 col M:S format a10

WITH t AS (
  SELECT 100 s FROM dual
  UNION ALL
  SELECT 7201 FROM dual)
SELECT s,TRUNC(s/60)||':'||mod(s,60) "M:S"
FROM t;
Another WITH Clause demo ... this one provided by Helen Lofstrom via email on 30-Aug-2011.

This demo shows how to use a CTE to provide the equivalent of multiple input parameters to a query outside of a stored procedure.		 WITH date_params AS (SELECT SYSDATE - 30 AS startdate, SYSDATE AS enddate FROM dual)
SELECT do.object_name, 'TABLE'
FROM dba_objects do, date_params dp
WHERE do.object_type = 'TABLE'
AND created BETWEEN dp.startdate AND dp.enddate
UNION ALL
SELECT do.object_name, 'INDEX'
FROM dba_objects do, date_params dp
WHERE do.object_type = 'INDEX'
AND created BETWEEN dp.startdate AND dp.enddate;

Related Topics
Conditions
Connect By
Select

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved