# 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

```sql
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

```sql
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;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FlP45usMUJE2TcnBVqJbU%2Fimage.png?alt=media&#x26;token=581631df-edff-402b-8d51-628a87ce7d02" alt=""><figcaption></figcaption></figure>

#### Example 2 (Joining a CTE with a table)

We will use `rental` and `staff` tables

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

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FmjWxE3AO3yZvBsDb6mhS%2Fimage.png?alt=media&#x26;token=edf24658-3b91-4b36-9cdf-e6ba15acb156" alt=""><figcaption></figcaption></figure>

#### Example 3 (CTE with a window function)

```sql
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;
```

<figure><img src="https://1920086362-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FDfv51K0WXLZdwTryHQZc%2Fuploads%2FYyLDkngJXPgzRHuOuDzF%2Fimage.png?alt=media&#x26;token=b1be2b73-f2d3-4b95-9fa5-0d28663ea823" alt=""><figcaption></figcaption></figure>

#### 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

```sql
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)

```sql
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;
```

***


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://notes.nomanaziz.me/development/database/sql/basics/8.-common-table-expressions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.