SQL Language Guide : 4. Elements of SQL Statements : Common Table Expressions (CTEs) : Examples of Using CTEs: The Person and Relative Tables
 
Share this page                  
Examples of Using CTEs: The Person and Relative Tables
The CTE examples in this section assume the following data in two tables:
Person table
 
 
Relatives table
 
Name
Gender
Name
Parent
Me
M
Me
Mom
Sis
F
Me
Dad
Dad
M
Sis
Mom
Mom
F
Sis
Dad
Bro
M
Bro
Mom
StSis
F
Bro
Dad
Uncle1
M
Dad
GrDad
Uncle2
M
Dad
GrMa
Aunt
F
StSis
Dad
GrDad
M
StSis
StMom
GrMa
F
Uncle1
GrDad
GrGrDad
M
Uncle1
GrMa
GrGrMa
F
Uncle2
GrDad
Son
M
Uncle2
GrMa
GrSon
M
Aunt
GrDad
Daugtr
F
Aunt
GrMa
GrDaugtr
F
GrDad
GrGrDad
Dog
F
GrDad
GrGrMa
StMom
F
Son
Me
Niece
F
GrSon
Son
Nephew
M
Daugtr
Me
Cousin
M
GrDaugtr
Son
 
 
Niece
Sis
 
 
Nephew
Bro
 
 
Cousin
Uncle1
With this data we can perform simple lookups that can be used to answer relationship questions.
Q: Who is the mother of whom?
To answer who is the mother of whom we can 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;
The ismother result set is returned:
ismother
name           child
------         -------
GrGrMa         GrDad
GrMa           Aunt
GrMa           Dad
GrMa           Uncle1
GrMa           Uncle2
Mom            Bro
Mom            Me
Mom            Sis
Sis            Niece
StMom          StSis
Referring to a Defined WITH Element
Q: Who are parents?
To determine who are parents we could simply apply a UNION where we needed it, but we can also define a further element, isparent:
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 ALL
        SELECT name,child FROM isfather
)
SELECT * FROM isparent
ORDER BY 1,2;
The isparent result set is returned:
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
Mom           Bro
Mom           Me
Mom           Sis
Sis           Niece
Son           GrDaugtr
Son           GrSon
StMom         StSis
Uncle1        Cousin
Because the dependencies are simple, the same result could be achieved with simple substitution into derived tables as shown here:
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 ALL
          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;
Using Nested WITH Element References
The following example introduces the first level of complexity.
Q: Who are grandparents?
Finding who are grandparents could be achieved 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.
For this to work, the instances of ismother and isfather must be distinct in the join from those expanded in isparent. Now it is seen why this acts like an inline view: Although the elements are defined once, nested references between these cause recursive replication of derived tables to 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 ALL
        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;
grandparents
name         col2           grchild
---------    -----------    -----------
Dad          isgrfather     Daugtr
Dad          isgrfather     Niece
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
Mom          isgrmother     Daugtr
Mom          isgrmother     Niece
Mom          isgrmother     Nephew
Mom          isgrmother     Son
Q: Who are brother and sister?
To further illustrate nested CTEs, we ask the question, "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 gives the answer:
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 ALL
        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
Q: Who are full siblings?
Noting that the previous result includes a few step relations suggests a query to determine only full siblings by joining 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 ALL
        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                Mom            Dad
Bro            Sis               Mom            Dad
Dad            Aunt              GrMa           GrDad
Dad            Uncle1            GrMa           GrDad
Dad            Uncle2            GrMa           GrDad
Me             Bro               Mom            Dad
Me             Sis               Mom            Dad
Sis            Bro               Mom            Dad
Sis            Me                Mom            Dad
Uncle1         Aunt              GrMa           GrDad
Uncle1         Dad               GrMa           GrDad
Uncle1         Uncle2            GrMa           GrDad
Uncle2         Aunt              GrMa           GrDad
Uncle2         Dad               GrMa           GrDad
Uncle2         Uncle1            GrMa           GrDad
Q: Who are aunts and uncles?
If we add elements isson and isdaughter then we can also derive hasnephew and hasniece based on the other tables.
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 ALL
        SELECT m.name, m.child FROM ismother m
),
sharefather(name,sibling,father) AS (
        SELECT p2.name,p3.name,a.name
        FROM person p2,person p3,isfather a JOIN isfather b ON a.name=b.name
        WHERE p2.name<>p3.name AND a.child=p2.name AND b.child=p3.name
),
sharemother(name,sibling,mother) AS (
        SELECT p2.name,p3.name,a.name
        FROM person p2, person p3,ismother a JOIN ismother b ON a.name=b.name
        WHERE p2.name<>p3.name AND a.child=p2.name AND b.child=p3.name
),
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 p1.name,ms.name,fs.name
        FROM person p1 LEFT OUTER JOIN isfather fs
                ON p1.name=fs.child
                        LEFT OUTER JOIN ismother ms
                ON p1.name=ms.child
        WHERE p1.gender='M'
),
isdaughter(name, mother, father) AS (
        SELECT p1.name,md.name,fd.name
        FROM person p1 LEFT OUTER JOIN isfather fd
                ON p1.name=fd.child
                        LEFT OUTER JOIN ismother md
                ON p1.name=md.child
        WHERE p1.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
),
hasniece(name, niece, sibling) AS (
        SELECT i1.name,d.name,i1.sibling
        FROM issibling i1 INNER JOIN isdaughter d
                ON i1.sibling=d.mother OR i1.sibling=d.father
)
SELECT 'F', name, niece AS rel, sibling FROM hasniece 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            Niece         Sis
F            Dad            Cousin        Uncle1
F            Me             Niece         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
WITH Elements in CREATE VIEW
The WITH syntax of a CTE is not only applicable to queries but can also appear in contexts like CREATE TABLE AS SELECT or CREATE VIEW. For example:
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 ALL
        SELECT m.name, m.child FROM ismother m
),
sharefather(name,sibling,father) AS (
        SELECT p2.name,p3.name,a.name
        FROM person p2,person p3,isfather a JOIN isfather b ON a.name=b.name
        WHERE p2.name<>p3.name AND a.child=p2.name AND b.child=p3.name
),
sharemother(name,sibling,mother) AS (
        SELECT p2.name,p3.name,a.name
        FROM person p2, person p3,ismother a JOIN ismother b ON a.name=b.name
        WHERE p2.name<>p3.name AND a.child=p2.name AND b.child=p3.name
),
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;
Note:  In this example the defined view name happens to be the same as one of the WITH elements in the query. There is no conflict because the WITH element names remain local to the query.