What is CTE (Common Table Expressions)
A common table expression (CTE) is a temporary result set accessible within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
CTE does not hold any physical space in database like a derived table. But Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
We can also create Nested CTE to staging our data throughout multiple CTE’s
Below are the scenarios where a CTE can best fit:
- Recursive Queries Using Common Table Expressions.
- Instead of creating a view for only one query we can choose CTE. As like view it will not take create it’s definition inside metadata.
- Making queries readable (For complex and long queries)
Advantages : –
Use of CTE offers improved readability and ease in maintenance of complex queries. The query can be divided into blocks … More