Sql-cte common table expressions and with usage summary

CTE (Common table expression) common table expressions, which are temporary result sets that are defined within the execution scope of a single statement and are valid only for the duration of the query. It can be self-referencing, and can be referenced multiple times in the same query, enabling the reuse of code snippets.

The biggest benefit of the CTE is the readability of the T-SQL code, which enables complex queries such as recursion in a more elegant and concise manner.

The CTE can be used to:
⒈ Create a recursive query, this should be the best place to use the CTE
⒉ A generated table multiple times in the same statement
3. Reduce sub-queries and table variables to improve execution efficiency

CTE Benefits:
1. Using a CTE gives you the benefit of improved readability and easy maintenance of complex queries. At the same time, the CTE is much more efficient than the table variable.
2. Can be used to define a SQL fragment that will be used by the entire SQL statement. Sometimes it is to make the SQL statement more readable, or it may be in different parts of union all as part of providing data.
3. Queries can be divided into individual blocks, simple blocks, and logical building blocks. These simple blocks can then be used to generate more complex temporary CTE until the final result set is generated.

Here is the syntax for the CTE:

With cte_name (column_name [,... n]) as (    cte_query_definition–is

Using the example
1. Querying a temporary result set

1  at )Select from CTE;

Note: 1. The SQL statement using the CTE should be immediately behind the relevant CTE.
2. Multiple CTE intermediate with commas, separated.
3. It can be used more than once with an SQL statement that follows, but cannot be used by multiple SQL statements that follow.

2. Create a recursive query

With CTE (CategoryID, Categoryname,parentid,categorylevel) as (SELECT Categoryid,categoryname,parentid, Categorylevelfrom Category (NOLOCK) WHERE Status1 in (A,  1) Select from CTE;

3. CTE result set and data Table Association

1  at Select from product P (NOLOCK) INNER join CTE C (NOLOCK) on P.categoryid=c. CategoryID

The above three, should be the CTE expression, the most common usage, if there are other uses, we are very happy to discuss together.

Sql-cte common table expressions and with usage summary