Login Register Actian.com  

Actian Community Wiki

Navigation
Learn About
Developing With
Ingres Talk
Information
Toolbox

Common Table Expressions

From Ingres Community Wiki

Jump to: navigation, search

Contents

References

ISO/IEC 9075-2:2003 Information technology — Database languages — SQL — Part 2: Foundation (SQL/Foundation)

Syntax

query ::= [ 'WITH' { <with_list_element> },... ] <SELECT-expr> ;

with_list_element ::= <table_name> [ '(' { <col-id> },... ')' ] 'AS' '(' <query_expr> ')' ;

Common Table Expression Overview

Common Table Expressions are a means of specifing factorised expressions from otherwise complex SQL. The syntax is part of top level SELECT syntax and thus can appear in contexts such as CREATE TABLE AS, DEFINE GLOBAL TEMPORARY TABLE AS, CREATE VIEW, INSERT INTO from SELECT as well as the standard query. They can also be thought of as inline views.

A simple example will give an idea of the syntax:

WITH
 t(z) AS (
        SELECT c FROM s WHERE i > 10
 )
SELECT t.z FROM t ORDER BY 1;

The t in the example is the name of the first (and in this case only) WITH Element. It is possible to have multiple WITH Elements in the same query. Each WITH Element may be referred to by its name as though it were a VIEW. The scope of each WITH Element extends rightwards through the rest of the SQL statement. WITH Elements may refer to others which have been declared already in the current statement. See below for an example:

WITH
 t(z) AS (
        SELECT c FROM s WHERE i > 10
 ),
 u(a,i) AS (
        SELECT z, 15 FROM t
 )
SELECT u.a,s.i FROM u,s WHERE s.i=u.i ORDER BY 1;

The way that they are used is that each time a WITH element name is referenced, either from the main query or another WITH element, a new derived table and correlation instance are created. This is simple when considering just WITH elements that contain only tables or views in their FROM lists but when one WITH element refers to another, there will be derived tables replicated for the full recursive set of referred to elements and not just the element itself.

Real Examples

As an example, create two small tables:

CREATE TABLE person(name VARCHAR(8), gender CHAR);
INSERT INTO person  VALUES('Me',        'M');
INSERT INTO person  VALUES('Sis',       'F');
INSERT INTO person  VALUES('Dad',       'M');
INSERT INTO person  VALUES('Mum',       'F');
INSERT INTO person  VALUES('Bro',       'M');
INSERT INTO person  VALUES('StSis',     'F');
INSERT INTO person  VALUES('Uncle1',    'M');
INSERT INTO person  VALUES('Uncle2',    'M');
INSERT INTO person  VALUES('Aunt',      'F');
INSERT INTO person  VALUES('GrDad',     'M');
INSERT INTO person  VALUES('GrMa',      'F');
INSERT INTO person  VALUES('GrGrDad',   'M');
INSERT INTO person  VALUES('GrGrMa',    'F');
INSERT INTO person  VALUES('Son',       'M');
INSERT INTO person  VALUES('GrSon',     'M');
INSERT INTO person  VALUES('Daugtr',    'F');
INSERT INTO person  VALUES('GrDaugtr',  'F');
INSERT INTO person  VALUES('Dog',       'F');
INSERT INTO person  VALUES('StMum',     'F');
INSERT INTO person  VALUES('Neice',     'F');
INSERT INTO person  VALUES('Nephew',    'M');
INSERT INTO person  VALUES('Cousin',    'F');

CREATE TABLE relatives(name VARCHAR(8),parent VARCHAR(8));
INSERT INTO relatives VALUES('Me',      'Mum');
INSERT INTO relatives VALUES('Me',      'Dad');
INSERT INTO relatives VALUES('Sis',     'Mum');
INSERT INTO relatives VALUES('Sis',     'Dad');
INSERT INTO relatives VALUES('Bro',     'Mum');
INSERT INTO relatives VALUES('Bro',     'Dad');
INSERT INTO relatives VALUES('Dad',     'GrDad');
INSERT INTO relatives VALUES('Dad',     'GrMa');
INSERT INTO relatives VALUES('StSis',   'Dad');
INSERT INTO relatives VALUES('StSis',   'StMum');
INSERT INTO relatives VALUES('Uncle1',  'GrDad');
INSERT INTO relatives VALUES('Uncle1',  'GrMa');
INSERT INTO relatives VALUES('Uncle2',  'GrDad');
INSERT INTO relatives VALUES('Uncle2',  'GrMa');
INSERT INTO relatives VALUES('Aunt',    'GrDad');
INSERT INTO relatives VALUES('Aunt',    'GrMa');
INSERT INTO relatives VALUES('GrDad',   'GrGrDad');
INSERT INTO relatives VALUES('GrDad',   'GrGrMa');
INSERT INTO relatives VALUES('Son',     'Me');
INSERT INTO relatives VALUES('GrSon',   'Son');
INSERT INTO relatives VALUES('Daugtr',  'Me');
INSERT INTO relatives VALUES('GrDaugtr','Son');
INSERT INTO relatives VALUES('Neice',   'Sis');
INSERT INTO relatives VALUES('Nephew',  'Bro');
INSERT INTO relatives VALUES('Cousin',  'Uncle1'); \g
This defines a relationship tree tha looks like this:

With this data we can perform simple lookups that can be used to answer relationship questions. For instance, if we wish to find out who is the mother of who we could write:

WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
)
SELECT * FROM ismother
ORDER BY 1,2;
ismother
name child
GrGrMaGrDad
GrMa Aunt
GrMa Dad
GrMa Uncle1
GrMa Uncle2
Mum Bro
Mum Me
Mum Sis
Sis Neice
StMum StSis

So far the examples have been trivial, single level substitutions but we can refer to WITH elements that are already defined in the statement. For instance, to determine who are parents we could simply apply a UNION where we needed it, but we can also put that into a further element:

WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT name,child FROM ismother
        UNION
        SELECT name,child FROM isfather
)
SELECT * FROM isparent
ORDER BY 1,2;
isparent
name child
Bro Nephew
Dad Bro
Dad Me
Dad Sis
Dad StSis
GrDad Aunt
GrDad Dad
GrDad Uncle1
GrDad Uncle2
GrGrDad GrDad
GrGrMaGrDad
GrMa Aunt
GrMa Dad
GrMa Uncle1
GrMa Uncle2
Me Daugtr
Me Son
Mum Bro
Mum Me
Mum Sis
Sis Neice
Son GrDaugtr
Son GrSon
StMum StSis
Uncle1Cousin

Even with that last example things are straight forward as the same could be achieved with simple substitution into derived tables as below as the dependancies are simple:

SELECT * FROM
        ( SELECT name,child FROM
                ( SELECT parent AS name,r.name AS child
                  FROM person p JOIN relatives r
                        ON p.name=r.parent AND gender='F'
                ) AS ismother
          UNION
          SELECT name,child FROM
                ( SELECT parent AS name,r.name AS child
                  FROM person p JOIN relatives r
                        ON p.name=r.parent AND gender='M'
                ) AS isfather
        ) AS isparent
ORDER BY 1,2;

[Also, by noting the implications that all the gender fiield restrictions are in effect undone by the UNION, the statement could be further simplified but we will overlook that for the sake of the example.]

The next example introduces the first real complexity. We will define the relationship for a grand parent which could be effected by a joining of two isparent references but as before we will define the gender specific elements and union them. The complexity arises when you notice that

  • ismother and isfather are both present in the definition of isparent
  • ismother and isfather are also both present in the definition of isgrfather and isgrmother both of which also refer to isparent.

Clearly for this to work the instances of ismother and isfather must be distinct in the join from those expanded in isparent. Now it will be seen why this acts like an inline view; Although the elements are defined once, nested references beween these cause recursive replication of derived tables to the the referenced elements.

WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT name,child FROM ismother
        UNION
        SELECT name,child FROM isfather
),
isgrfather(name, grchild) AS (
        SELECT f.name,p.child
        FROM isfather f JOIN isparent p ON f.child=p.name
),
isgrmother(name, grchild) AS (
        SELECT f.name,p.child
        FROM ismother f JOIN isparent p ON f.child=p.name
)
SELECT name,'isgrfather',grchild FROM isgrfather
UNION ALL
SELECT name,'isgrmother',grchild FROM isgrmother
ORDER BY 2,1,3;
grand parents
name col2 grchild
Dad isgrfatherDaugtr
Dad isgrfatherNeice
Dad isgrfatherNephew
Dad isgrfatherSon
GrDad isgrfatherBro
GrDad isgrfatherCousin
GrDad isgrfatherMe
GrDad isgrfatherSis
GrDad isgrfatherStSis
GrGrDad isgrfatherAunt
GrGrDad isgrfatherDad
GrGrDad isgrfatherUncle1
GrGrDad isgrfatherUncle2
Me isgrfatherGrDaugtr
Me isgrfatherGrSon
GrGrMaisgrmotherAunt
GrGrMaisgrmotherDad
GrGrMaisgrmotherUncle1
GrGrMaisgrmotherUncle2
GrMa isgrmotherBro
GrMa isgrmotherCousin
GrMa isgrmotherMe
GrMa isgrmotherSis
GrMa isgrmotherStSis
Mum isgrmotherDaugtr
Mum isgrmotherNeice
Mum isgrmotherNephew
Mum isgrmotherSon

Feeling like we have a grasp of this, let's ask the question of who are brother and sister. We can define a sharefather (and a sharemother) by checking each pairing of persons with whether there exists a third person such that the relationship isfather (or ismother) holds for both. Taking a union of these will give the answer. We would of course find that each person satisfied a sharefather with them selves and that each real pairing would be reported twice such as "Bro,Sis" and "Sis,Bro" so we will eliminate such duplicates by comparison.

WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT f.name, f.child FROM isfather f
        UNION
        SELECT m.name, m.child FROM ismother m
),
sharefather(name, sibling, father) AS (
        SELECT a.child,b.child,a.name
        FROM isfather a JOIN isfather b
                ON a.name=b.name AND a.child<b.child
),
sharemother(name, sibling, mother) AS (
        SELECT a.child,b.child,a.name
        FROM ismother a JOIN ismother b
                 ON a.name=b.name AND a.child<b.child
)
SELECT name, sibling FROM sharefather UNION
SELECT name, sibling FROM sharemother ORDER BY 1,2;
issibling
name sibling
Aunt Dad
Aunt Uncle1
Aunt Uncle2
Bro Me
Bro Sis
Bro StSis
Dad Uncle1
Dad Uncle2
Daugtr Son
GrDaugtrGrSon
Me Sis
Me StSis
Sis StSis
Uncle1 Uncle2

Noting that this includes a few step relations suggests a query to determine just full siblings by joning sharefather and sharemother:

WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT f.name, f.child FROM isfather f
        UNION
        SELECT m.name, m.child FROM ismother m
),
sharefather(name, sibling, father) AS (
        SELECT a.child,b.child,a.name
        FROM isfather a JOIN isfather b
                ON a.name=b.name AND a.child<b.child
),
sharemother(name, sibling, mother) AS (
        SELECT a.child,b.child,a.name
        FROM ismother a JOIN ismother b
                 ON a.name=b.name AND a.child<b.child
),
issibling(name,sibling,mother,father) AS (
        SELECT sf.name,sf.sibling,sm.mother,sf.father
        FROM sharefather sf JOIN sharemother sm
        ON sf.name=sm.name AND sf.sibling=sm.sibling
)
SELECT * FROM issibling
ORDER BY 1, 2;
full siblings
name sibling mother father
Aunt Dad GrMa GrDad
Aunt Uncle1 GrMa GrDad
Aunt Uncle2 GrMa GrDad
Bro Me Mum Dad
Bro Sis Mum Dad
Dad Uncle1 GrMa GrDad
Dad Uncle2 GrMa GrDad
Me Sis Mum Dad
Uncle1 Uncle2 GrMa GrDad

If we add add elements isson and isdaughter then we can also derive hasnephew and hasniece and based on the other tables. Note that in this case we want "issibling" to deliver both "Bro,Sis" and "Sis,Bro" so "sharemother" and "sharefather" are adjusted accordingly.

WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT f.name, f.child FROM isfather f
        UNION
        SELECT m.name, m.child FROM ismother m
),
sharefather(name, sibling, father) AS (
        SELECT a.child,b.child,a.name
        FROM isfather a JOIN isfather b
                ON a.name=b.name AND a.child<>b.child
),
sharemother(name, sibling, mother) AS (
        SELECT a.child,b.child,a.name
        FROM ismother a JOIN ismother b
                 ON a.name=b.name AND a.child<>b.child
),
issibling AS (
        SELECT sf.name,sf.sibling,sf.father,sm.mother
        FROM sharefather sf JOIN sharemother sm
        ON sf.name=sm.name AND sf.sibling=sm.sibling
),
isson(name, mother, father) AS (
        SELECT p.name,ms.name,fs.name
        FROM person p LEFT OUTER JOIN isfather fs
                ON p.name=fs.child
                        LEFT OUTER JOIN ismother ms
                ON p.name=ms.child
        WHERE p.gender='M'
),
isdaughter(name, mother, father) AS (
        SELECT p.name,md.name,fd.name
        FROM person p LEFT OUTER JOIN isfather fd
                 ON p.name=fd.child
                        LEFT OUTER JOIN ismother md
                ON p.name=md.child
        WHERE p.gender='F'
),
hasnephew(name, nephew, sibling) AS (
        SELECT i.name,s.name,i.sibling
        FROM issibling i INNER JOIN isson s
                ON i.sibling=s.mother OR i.sibling=s.father
),
hasneice(name, neice, sibling) AS (
        SELECT i.name,d.name,i.sibling
        FROM issibling i INNER JOIN isdaughter d
                ON i.sibling=d.mother OR i.sibling=d.father
)
SELECT 'F', name, neice AS rel, sibling FROM hasneice UNION
SELECT 'M', name, nephew AS rel, sibling FROM hasnephew
ORDER BY 1,2,3;
aunts+uncles
col1 name rel sibling
F Aunt Cousin Uncle1
F Aunt Sis Dad
F Aunt StSis Dad
F Bro Daugtr Me
F Bro Neice Sis
F Dad Cousin Uncle1
F Me Neice Sis
F Sis Daugtr Me
F Uncle1 Sis Dad
F Uncle1 StSis Dad
F Uncle2 Cousin Uncle1
F Uncle2 Sis Dad
F Uncle2 StSis Dad
M Aunt Bro Dad
M Aunt Me Dad
M Bro Son Me
M Me Nephew Bro
M Sis Nephew Bro
M Sis Son Me
M Uncle1 Bro Dad
M Uncle1 Me Dad
M Uncle2 Bro Dad
M Uncle2 Me Dad

The WITH syntax is not only applicable to queries but can also appear just before any outer level SELECT keyword such as here with CREATE VIEW:

CREATE VIEW issibling AS
WITH
ismother(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='F'
),
isfather(name, child) AS (
        SELECT parent,r.name
        FROM person p JOIN relatives r ON p.name=r.parent AND gender='M'
),
isparent(name, child) AS (
        SELECT f.name, f.child FROM isfather f
        UNION
        SELECT m.name, m.child FROM ismother m
),
sharefather(name, sibling, father) AS (
        SELECT a.child,b.child,a.name
        FROM isfather a JOIN isfather b
                ON a.name=b.name AND a.child<>b.child
),
sharemother(name, sibling, mother) AS (
        SELECT a.child,b.child,a.name
        FROM ismother a JOIN ismother b
                 ON a.name=b.name AND a.child<>b.child
),
issibling AS (
        SELECT sf.name,sf.sibling,sf.father,sm.mother
        FROM sharefather sf JOIN sharemother sm
        ON sf.name=sm.name AND sf.sibling=sm.sibling
)
SELECT * FROM issibling;

As you will observe, the VIEW was created with the name "issibling" and the VIEW definition also used the same name - there is no conflict as the scope of the WITH Elements names are local to the query.

Feature set T131 Future

Feature set T131 is not yet implemented but if it were we could extend our SQL examples to add isancestor and isdecendant

WITH RECURSIVE
isancestor(name,antecedant,lvl) AS (
        SELECT r.name, r.parent, 1
        FROM relatives r
        UNION ALL
        SELECT isanc.name, r2.parent, isanc.lvl+1
        FROM isancestor isanc INNER JOIN relatives r2
                ON isanc.antecedant=r2.name
)
SELECT * FROM isancestor WHERE name='GrDaugtr'
ORDER BY 1,3,2;

isancestor
name antecedant lvl
GrDaugtrSon 1
GrDaugtrMe 2
GrDaugtrDad 3
GrDaugtrMum 3
GrDaugtrGrDad 4
GrDaugtrGrMa 4
GrDaugtrGrGrDad 5
GrDaugtrGrGrMa 5


WITH RECURSIVE
isdecendant(name,decendant,lvl) AS (
        SELECT r.parent, r.name, -1
        FROM relatives r
        UNION ALL
        SELECT isdesc.name, r2.name, isdesc.lvl-1
        FROM isdecendant isdesc INNER JOIN relatives r2
                ON isdesc.decendant=r2.parent
)
SELECT * FROM isdescendant WHERE name='GrGrDad'
ORDER BY 1,3 DESC,2;
isdescendant
name decendant lvl
GrGrDadGrDad -1
GrGrDadAunt -2
GrGrDadDad -2
GrGrDadUncle1 -2
GrGrDadUncle2 -2
GrGrDadBro -3
GrGrDadCousin -3
GrGrDadMe -3
GrGrDadSis -3
GrGrDadStSis -3
GrGrDadDaugtr -4
GrGrDadNeice -4
GrGrDadNephew -4
GrGrDadSon -4
GrGrDadGrDaugtr -5
GrGrDadGrSon -5

Documentation

Not presently documented.

Customer issues

No external issues. Mantis bug 742 and SD 144909 were raised from Vectorwise testing. Bug 123828: An incorrect name scope error is raised if two WITH clause cubselects contain the same column name.


Extent of implementation

9.3 and 10.0 have support for the SQL but with the implementation limited to single level references.

The change 505884 in main fixes bug 123828 and completes the full range replication needed for multiple levels of references.

Changes 507804 and 507874 extend the support CTE to embedded SQL.

The functionality implemented covers feature set T121 - "WITH (excluding RECURSIVE) in query expression".

The WITH clause has further feature sets T122, T131 and T132 relating to it. T131 extends the WITH clause to support WITH RECURSIVE clause that allows recursive WITH elements. T122 and T132 extend the WITH syntax to subqueries for T121 and T131 respectivel

Test Considerations

OS Dependencies

None

Personal tools
© 2011 Actian Corporation. All Rights Reserved