8. Common Table Expressions

PostgreSQL CTE

(Introduce us to PostgreSQL common table expressions or CTEs)

• A CTE is a temporary result set which you can reference within another SQL statement including SELECT, INSERT, UPDATE or DELETE

• They only exist during execution of the query

• Its syntax is

WITH cte_name (column_list) AS (
	CTE_query_definition
)
statement;

• In its 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 specify the column list after CTE name, the select list of the CTE_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 SELECT, INSERT, UPDATE or DELETE

• CTE are commonly used to simplify complex joins and subqueries in PostgreSQL

Example 1

We will use film and rental tables from sample db

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;

Example 2 (Joining a CTE with a table)

We will use rental and staff tables

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);

Example 3 (CTE with a 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;

PostgreSQL CTE advantages

• 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

• 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.

---

Recursive query using CTEs

(discuss the recursive query and learn how to apply it in various contexts)

• A recursive query is a query that refers to a recursive CTE

• They are useful in many situations such as querying heirarchical data like organizational structure, bill of materials, etc

• This is the syntax of recursive CTE

WITH RECURSIVE cte_name AS (
	CTE_query_definition -- non-recursive-term
	UNION [ALL]
	CTE_query_definition -- recursive-term
)

• This syntax has three elements

  1. non-recursive-term: the non-recursive term is a CTE query definition that forms the base result set of the CTE structure

  2. recursive-term: the recursive term is one or more CTE query definitions joined with the non-recursive term using the UNION or UNION ALL operator. The recursive term references the CTE name itself

  3. termination-check: the recursive term references the CTE name itselfg

Example (get all subordinates of the manager with the id 2)

WITH RECURSIVE subordinates AS (
	SELECT
		employee_id,
		manager_id,
		full_name
	FROM
		employees
	WHERE
		employee_id = 2
	UNION
		SELECT
			e.employee_id,
			e.manager_id,
			e.full_name
		FROM
			employees e
		INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT
		*
  FROM
		subordinates;

---