Data Glossary 🧠
What is a CTE (Common Table Expression)?
A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. The CTE can also be used in a View.
# Types: Recursive and Non-Recursive
# Non-Recursive CTE
There are two types of CTEs: Recursive and Non-Recursive.
The non-recursive are simple where CTE is used to avoid SQL duplication by referencing a name instead of the actual SQL statement.
# Recursive CTE
Recursive CTEs use repeated procedural loops therefore the recursion. The recursive query calls itself until the query satisfied the condition. In a recursive CTE, we should provide a where condition to terminate the recursion.
A recursive CTE is useful in querying hierarchical data such as organization charts where one employee reports to a manager or multi-level bill of materials when a product consists of many components, and each component itself also consists of many other components.
See more on 5 Practical SQL CTE Examples | LearnSQL.com.