Common Table Expressions (CTEs)
if you need to reference/join the same data set multiple times you can do so by defining a CTE. Therefore, it can be a form of code re-use.
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.
CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190766(v=sql.105)?redirectedfrom=MSDN).
A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.
A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents (https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186243(v=sql.105)?redirectedfrom=MSDN).
Syntax of declaring CTE(Common table expression) :
with [Name of CTE]
as ( Body of common table expression )
SQL_statement;
In this syntax:
First, specify the expression name (expression_name) to which you can refer later in a query.
Next, specify a list of comma-separated columns after the expression_name. The number of columns must be the same as the number of columns defined in the CTE_definition.
Then, use the AS keyword after the expression name or column list if the column list is specified.
After, define a SELECT statement whose result set populates the common table expression.
Finally, refer to the common table expression in a query (SQL_statement) such as SELECT, INSERT, UPDATE, DELETE, or MERGE.
We prefer to use common table expressions rather than to use subqueries because common table expressions are more readable (https://www.sqlservertutorial.net/sql-server-basics/sql-server-cte).
There are several reasons why you may want to use a CTE over other methods. Some of them include:
Readability – CTE’s promote readability. Rather than lump all you query logic into one large query, create several CTE’s, which are the combined later in the statement. This lets you get the chunks of data you need and combine them in a final SELECT.
Substitute for a View – You can substitute a CTE for a view. This is handy if you don’t have permissions to create a view object or you don’t want to create one as it is only used in this one query.
Recursion – Use CTE’s do create recursive queries, that is queries that can call themselves. This is handy when you need to work on hierarchical data such as organization charts.
Limitations – Overcome SELECT statement limitations, such as referencing itself (recursion), or performing GROUP BY using non-deterministic functions.
Ranking – Whenever you want to use ranking function such as ROW_NUMBER(), RANK(), NTILE() etc.
Reference:
https://www.essentialsql.com/introduction-common-table-expressions-ctes
https://chartio.com/resources/tutorials/using-common-table-expressions
For example, a non-recursive CTE is basically a query-local VIEW. There are several advantages and caveats to them. The syntax is more readable than nested FROM (SELECT ...). A CTE can refer to another and it can be referenced from multiple places. (https://mariadb.com/kb/en/non-recursive-common-table-expressions-overview).
On the other hand, a hierarchy structure as follows is typically queried via a recursive common table expression (CTE) such as the following:
https://www.mssqltips.com/sqlservertip/5379/sql-server-common-table-expressions-cte-usage-and-examples
If you would like to read more about CTEs, the following article by Robert Shelton on Simple-Talk gives a very easy-to-follow description of using them, including recursive CTEs: https://www.simple-talk.com/sql/t-sqlprogramming/sql-server-cte-basics.