August 7, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL CTE (common table expressions) to simplify complex queries.
Table of Contents
Introduction to PostgreSQL common table expressions or CTEs
A common table expression is a temporary result set which you can reference within another SQL statement including SELECT
, INSERT
, UPDATE or DELETE
.
Common Table Expressions are temporary in the sense that they only exist during the execution of the query.
The following shows the syntax of creating a CTE:
WITH cte_name (column_list) AS (
CTE_query_definition
)
statement;
In this syntax:
- First, specify the name of the CTE following by an optional column list.
- Second, inside the body of the
WITH
clause, specify a query that returns a result set. If you do not explicitly specify the column list after the CTE name, the select list of theCTE_query_definition
will become the column list of the CTE. - Third, use the CTE like a table or view in the
statement
which can be aSELECT
,INSERT
,UPDATE
, orDELETE
.
Common Table Expressions or CTEs are typically used to simplify complex joins and subqueries in PostgreSQL.
PostgreSQL CTE examples
Let’s take some examples of using CTEs to get a better understanding.
A simple PostgreSQL CTE example
We will use the film
and rental
tables from the sample database for the demonstration.
See the following example:
WITH cte_film AS (
SELECT
film_id,
title,
(CASE
WHEN length < 30 THEN 'Short'
WHEN length < 90 THEN 'Medium'
ELSE 'Long'
END) length
FROM
film
)
SELECT
film_id,
title,
length
FROM
cte_film
WHERE
length = 'Long'
ORDER BY
title;
Here is the partial output:
Note that this example is for the demonstration purpose.
In this example, we first defined a common table expression named cte_film
using the WITH
clause as follows:
WITH cte_film AS (
SELECT
film_id,
title,
(CASE
WHEN length < 30
THEN 'Short'
WHEN length >= 30 AND length < 90
THEN 'Medium'
WHEN length >= 90
THEN 'Long'
END) length
FROM
film
)
The common table expression has two parts:
- The first part defines the name of the CTE which is
cte_film
. - The second part defines a
SELECT
statement that populates the expression with rows.
We then used the cte_film
CTE in the SELECT
statement to return only films whose lengths are ‘Long’.
Joining a CTE with a table example
In the following example, we will use the rental
and staff
tables:
The following statement illustrates how to join a CTE with a table:
WITH cte_rental AS (
SELECT staff_id,
COUNT(rental_id) rental_count
FROM rental
GROUP BY staff_id
)
SELECT s.staff_id,
first_name,
last_name,
rental_count
FROM staff s
INNER JOIN cte_rental USING (staff_id);
In this example:
- First, the CTE returns a result set that includes staff id and the number of rentals.
- Then, join the
staff
table with the CTE using thestaff_id
column.
Here is the output:
Using CTE with a window function example
The following statement illustrates how to use the CTE with the RANK()
window function:
WITH cte_film AS (
SELECT film_id,
title,
rating,
length,
RANK() OVER (
PARTITION BY rating
ORDER BY length DESC)
length_rank
FROM
film
)
SELECT *
FROM cte_film
WHERE length_rank = 1;
In this example:
- First, we defined a CTE that returns the film ranking by length for each film rating.
- Second, we selected only films whose length rankings are one.
The following picture shows the output:
PostgreSQL CTE advantages
The following are some advantages of using common table expressions or CTEs:
- Improve the readability of complex queries. You use CTEs to organize complex queries in a more organized and readable manner.
- Ability to create recursive queries. Recursive queries are queries that reference themselves. The recursive queries come in handy when you want to query hierarchical data such as organization chart or bill of materials.
- Use in conjunction with window functions. You can use CTEs in conjunction with window functions to create an initial result set and use another select statement to further process this result set.
In this tutorial, you have learned about the PostgreSQL CTE or common table expression to simplify complex queries.