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.

123

WITH cte_query AS(SELECT … subquery ...)SELECT main query ... FROM/JOIN with cte_query ...

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:

12345678

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_storeFROM orders oJOIN avg_per_store avgON o.store = avg.store;

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 910111213141516171819202122

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