MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Magic WITH Clause

without comments

Magic WITH Clause

Learning Outcomes

  • Learn how to use the WITH clause.
  • Learn how to join the results of two WITH clauses.

Lesson Materials

The idea of modularity is important in every programming environment. SQL is no different than other programming languages in that regard. SQL-92 introduced the ability to save queries as views. Views are effectively modular views of data.

A view is a named query that is stored inside the data dictionary. The contents of the view change as the data in the tables that are part of the view changes.

SQL:1999 added the WITH clause, which defines statement scoped views. Statement scoped views are named queries, or queries named as views, only in the scope of a query where they are defined.

The simplest prototype for a WITH clause that contains a statement scoped view is:

WITH query_name
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
  SELECT column1, column2, ...
  FROM   table_name tn INNER JOIN query_name qn
  ON     tn.column_name = qn.column_name 
  WHERE  qn.column_name = 'Some literal';

You should note that the list of columns after the query name is an optional list. The list of columns must match the SELECT-list, which is the set of comma delimited columns of the SELECT clause.

A more complete prototype for a WITH clause shows you how it can contain two or more statement scoped views. That prototype is:

WITH query_name
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
, query_name2
[(column1, column2, ...)] AS
 (SELECT column1, column2, ...)
SELECT column1, column2, ...
FROM   table_name tn INNER JOIN query_name1 qn1
ON     tn.column_name = qn1.column_name INNER JOIN query_name2 qn2
ON     qn1.column_name = qn2.column_name;
WHERE  qn1.column_name = 'Some literal';

The WITH clause has several advantages over embedded view in the FROM clause or subqueries in various parts of a query or SQL statement. The largest advantage is that a WITH clause is a named subquery and you can reference it from multiple locations in a query; whereas, embedded subqueries are unnamed blocks of code and often results in replicating a single subquery in multiple locations.

A small model of three tables lets you test a WITH clause in the scope of a query. It creates a war, country, and ace tables. The tables are defined as:

WAR

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
WAR_ID                                    NUMBER
WAR_NAME                                  VARCHAR2(30)

COUNTRY

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
COUNTRY_ID                                NUMBER
COUNTRY_NAME                              VARCHAR2(20)

ACE

Name                             NULL?    TYPE
-------------------------------- -------- ----------------
ACE_ID                                    NUMBER
ACE_NAME                                  VARCHAR2(30)
COUNTRY_ID                                NUMBER
WAR_ID                                    NUMBER

The following WITH clause includes two statement scoped views. One statement scoped view queries results form a single table while the other queries results from a join between the country and ace tables.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
CLEAR COLUMNS
CLEAR BREAKS
 
BREAK ON REPORT
BREAK ON war_name SKIP PAGE
 
COL ace_id        FORMAT 9999 HEADING "Ace|ID #"
COL ace_name      FORMAT A24  HEADING "Ace Name"
COL war_name      FORMAT A12  HEADING "War Name"
COL country_name  FORMAT A14  HEADING "Country Name"
WITH wars (war_id, war_name) AS
 (SELECT w.war_id, war_name
  FROM   war w )
, aces (ace_id, ace_name, country_name, war_id) AS
 (SELECT   a.ace_id
  ,        a.ace_name
  ,        c.country_name
  ,        a.war_id
  FROM     ace a INNER JOIN country c
  ON       a.country_id = c.country_id)
SELECT   a.ace_id
,        a.ace_name
,        w.war_name
,        a.country_name
FROM     aces a INNER JOIN wars w
ON       a.war_id = w.war_id
ORDER BY war_name
,        CASE
           WHEN REGEXP_INSTR(ace_name,' ',1,2,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,2,1),LENGTH(ace_name) - REGEXP_INSTR(ace_name,' ',1,2,0))
           WHEN REGEXP_INSTR(ace_name,' ',1,1,1) > 0 THEN
             SUBSTR(ace_name,REGEXP_INSTR(ace_name,' ',1,1,1),LENGTH(ace_name))
         END;

wars is the first statement scoped view of the war table. aces is the second statement scoped view of the inner join between the ace and country tables. You should note that aces statement scoped view has access to the wars scoped view, and the master SELECT statement has scope access to both statement scoped views and any tables in its schema.

The query returns the following with the help of SQL*Plus formatting BREAK statements:

  Ace
 ID # Ace Name		       War Name     Country Name
----- ------------------------ ------------ --------------
 1009 William Terry Badham     World War I  America
 1003 Albert Ball			    United Kingdom
 1010 Charles John Biddle		    America
 1005 William Bishop			    Canada
 1007 Keith Caldwell			    New Zealand
 1006 Georges Guynemer			    France
 1008 Robert Alexander Little		    Austrailia
 1001 Manfred von Richtofen		    Germany
 1002 Eddie Rickenbacker		    America
 1004 Werner Voss			    Germany
 
  Ace
 ID # Ace Name		       War Name     Country Name
----- ------------------------ ------------ --------------
 1018 Richard Bong	       World War II America
 1015 Edward F Charles			    Canada
 1020 Heinrich Ehrler			    Germany
 1019 Ilmari Juutilainen		    Finland
 1014 Ivan Kozhedub			    Soviet Union
 1012 Thomas McGuire			    America
 1013 Pat Pattle			    United Kingdom
 1011 Erich Rudorffer			    Germany
 1016 Stanislaw Skalski 		    Poland
 1017 Teresio Vittorio			    Italy
 
20 rows selected.

The WITH clause is the most effective solution when you have a result set that needs to be consistently used in two or more places in a master query. That’s because the result set becomes a named statement scoped view.

Script Code

Click the Script Code link to open the test case seeding script inside the current webpage.

Written by maclochlainn

May 12th, 2022 at 7:01 pm