Navigation

Learn About

Developing With

Ingres Talk

Information

Toolbox

Search

Views

Common Table Expressions

From Ingres Community Wiki

Jump to: navigation, search

1 References
2 Syntax
3 Common Table Expression Overview
4 Real Examples
- 4.1 Feature set T131 Future
- 4.2 Documentation
5 Customer issues

[edit]

References

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

[edit]

Syntax

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

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

[edit]

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.

[edit]

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
GrGrMa	GrDad
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
GrGrMa	GrDad
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
Uncle1	Cousin

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	isgrfather	Daugtr
Dad	isgrfather	Neice
Dad	isgrfather	Nephew
Dad	isgrfather	Son
GrDad	isgrfather	Bro
GrDad	isgrfather	Cousin
GrDad	isgrfather	Me
GrDad	isgrfather	Sis
GrDad	isgrfather	StSis
GrGrDad	isgrfather	Aunt
GrGrDad	isgrfather	Dad
GrGrDad	isgrfather	Uncle1
GrGrDad	isgrfather	Uncle2
Me	isgrfather	GrDaugtr
Me	isgrfather	GrSon
GrGrMa	isgrmother	Aunt
GrGrMa	isgrmother	Dad
GrGrMa	isgrmother	Uncle1
GrGrMa	isgrmother	Uncle2
GrMa	isgrmother	Bro
GrMa	isgrmother	Cousin
GrMa	isgrmother	Me
GrMa	isgrmother	Sis
GrMa	isgrmother	StSis
Mum	isgrmother	Daugtr
Mum	isgrmother	Neice
Mum	isgrmother	Nephew
Mum	isgrmother	Son

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
GrDaugtr	GrSon
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.

[edit]

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
GrDaugtr	Son	1
GrDaugtr	Me	2
GrDaugtr	Dad	3
GrDaugtr	Mum	3
GrDaugtr	GrDad	4
GrDaugtr	GrMa	4
GrDaugtr	GrGrDad	5
GrDaugtr	GrGrMa	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
GrGrDad	GrDad	-1
GrGrDad	Aunt	-2
GrGrDad	Dad	-2
GrGrDad	Uncle1	-2
GrGrDad	Uncle2	-2
GrGrDad	Bro	-3
GrGrDad	Cousin	-3
GrGrDad	Me	-3
GrGrDad	Sis	-3
GrGrDad	StSis	-3
GrGrDad	Daugtr	-4
GrGrDad	Neice	-4
GrGrDad	Nephew	-4
GrGrDad	Son	-4
GrGrDad	GrDaugtr	-5
GrGrDad	GrSon	-5

[edit]

Documentation

Not presently documented.

[edit]

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.

[edit]

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

[edit]

Test Considerations

[edit]

OS Dependencies

None

Retrieved from "http://community.actian.com/wiki/Common_Table_Expressions"

Categories: DBMS Projects | Specifications | Projects

Actian Community Wiki

Navigation

Learn About

Developing With

Ingres Talk

Information

Toolbox

Search

Views

Common Table Expressions

From Ingres Community Wiki

Contents

References

Syntax

Common Table Expression Overview

Real Examples

Feature set T131 Future

Documentation

Customer issues

Extent of implementation

Test Considerations

OS Dependencies

Personal tools

Actian Community Wiki

Navigation vBmenu.register("wikinav");

Learn About vBmenu.register("learnabout");

Developing With vBmenu.register("devwith");

Ingres Talk vBmenu.register("ingtalk");

Information vBmenu.register("infonav");

Toolbox vBmenu.register("toolbox");

Search vBmenu.register("wikisearch");

Views

Common Table Expressions

From Ingres Community Wiki

Contents

References

Syntax

Common Table Expression Overview

Real Examples

Feature set T131 Future

Documentation

Customer issues

Extent of implementation

Test Considerations

OS Dependencies

Personal tools

Navigation

Learn About

Developing With

Ingres Talk

Information

Toolbox

Search