🧠 Second Brain

Search

Search IconIcon to open search

Common Table Expression (CTE)

Last updated Feb 9, 2024

A Common Table Expression (CTE) is a temporary, named result set, which can be referenced within SELECT, INSERT, UPDATE, or DELETE statements. CTEs are also usable in Views.

1
2
3
WITH cte_query AS
(SELECT  subquery ...)
SELECT main query ... FROM/JOIN with cte_query ...

# Types of CTEs: Recursive and Non-Recursive

# Non-Recursive CTE

There are two main types of CTEs: Recursive and Non-Recursive.

Non-recursive CTEs are simpler, used to reduce SQL duplication by replacing repeated SQL statements with a reference name.

Example:

1
2
3
4
5
6
7
8
WITH avg_per_store AS
  (SELECT store, AVG(amount) AS average_order
   FROM orders
   GROUP BY store)
SELECT o.id, o.store, o.amount, avg.average_order AS avg_for_store
FROM orders o
JOIN avg_per_store avg
ON o.store = avg.store;

# Recursive CTE

Recursive CTEs incorporate repeated procedural loops, hence the term “recursive.” These CTEs call themselves until a specified condition is met. In a recursive CTE, a termination condition is crucial to avoid infinite recursion.

Recursive CTEs are particularly useful for querying hierarchical data, such as organizational charts (where employees report to managers) or multi-level bills of materials (where products comprise multiple components, each of which may also consist of other components).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
WITH levels AS (
  SELECT
    id,
    first_name,
    last_name,
    superior_id,
    1 AS level
  FROM employees
  WHERE superior_id IS NULL
  UNION ALL
  SELECT
    employees.id,
    employees.first_name,
    employees.last_name,
    employees.superior_id,
    levels.level + 1
  FROM employees, levels
  WHERE employees.superior_id = levels.id
)
 
SELECT *
FROM levels;

For more details, visit 5 Practical SQL CTE Examples | LearnSQL.com.


Origin: 5 Practical SQL CTE Examples | LearnSQL.com.
References:
Created 2023-10-31