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