A Common Table Expression (CTE) is a temporary named result set, derived from a simple query and defined within the execution scope of a SELECT or INSERT statement.
After a CTE is defined, it can be referenced like a table or view can in a SELECT or INSERT statement. A CTE can also be used in a ...AS SELECT or CREATE VIEW statement as part of its defining SELECT statement.
A CTE is similar to a derived table in that it is not stored and lasts only for the duration of the query. Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query.
Using a CTE makes complex queries easier to read and maintain. Because a CTE can be referred to multiple times in a query, syntax can be simpler. You can divide the query into separate building blocks, which you can then use to build more complex CTEs until the final result set is returned.
Among other things, a CTE may be useful when you need to join the results of a GROUP BY with another table.
This simple example shows the components of a common table expression.
s (c INT, i INT);
INSERT INTO s VALUES (10,1),(20,2),(30,3),(100,15),(200,15),(300,15);
COMMIT;
/* Define the CTE expression and column list */
WITH t(z) AS
/* Define the CTE query */
(
SELECT c FROM s WHERE i > 10
)
/*Define outer query that references the CTE name */
SELECT t.z FROM t ORDER BY 1;
Executing . . .
z
-------------
100
200
300
(3 rows)
The t is the name of the first (and in this case only) WITH element (also known as a CTE). There can be multiple WITH elements in the same query. Each WITH element can be referred to by its name as though it were a view. The scope of each WITH element extends to the right through the rest of the SQL statement. WITH elements can refer to others that have been declared already in the current statement. For example:
Each time a WITH element name is referenced, either from the main query or from another WITH element, a new derived table and correlation instance are created.
This is simple when a WITH element contains only a table or view in its FROM list. When one WITH element refers to another, however, derived tables are replicated for the full recursive set of referred to elements and not only for the element itself.
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'
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 isparentreferences but, as before, we will define the gender specific elements and union them.
The complexity arises when you notice that:
ismotherand isfatherare both present in the definition of isparent.
ismotherand isfatherare also both present in the definition of isgrfatherand isgrmother, both of which also refer to isparent.
For this to work, the instances of ismotherand isfathermust 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 sharefatherand 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'
The WITH syntax of a CTE is not only applicable to queries but can also appear in contexts like 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.