4. Understanding the Elements of SQL Statements : Common Table Expressions (CTEs) : Examples of Using CTEs: The Person and Relative Tables : Using Nested WITH Element References
 
Share this page                  
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