Summary of CTE (Common Table Expression) in SQL

1. Meaning of WITH

The with as phrase, also known AS subquery factoring, allows you to do a lot of things and define an SQL segment, which 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 the provided data.
Especially useful for union all. Because each part of union all may be the same, but if each part is executed once, the cost is too high, so you can use the with as phrase, you only need to execute it once. If the table name defined by the with as phrase is called more than twice, the optimizer automatically puts the data obtained by the with as phrase into a TEMP table. If it is called only once, no. The prompt materialize is to forcibly put the data in the with as phrase into a global temporary table. This method improves the speed of many queries.

Ii. Usage

First, let's look at the following nested query statement:
Copy codeThe Code is as follows:
Select * from person. StateProvince where CountryRegionCode in
(Select CountryRegionCode from person. CountryRegion where Name like 'C % ')

The preceding query statement uses a subquery. Although this SQL statement is not complex, if there are too many layers of nesting, it will make the SQL statement very difficult to read and maintain. Therefore, you can use table variables to solve this problem. The SQL statement is as follows:
Copy codeThe Code is as follows:
Declare @ t table (CountryRegionCode nvarchar (3 ))
Insert into @ t (CountryRegionCode) (select CountryRegionCode from person. CountryRegion where Name like 'C % ')
Select * from person. StateProvince where CountryRegionCode
In (select * from @ t)

Although the preceding SQL statement is more complex than the first method, the subquery is placed in the table variable @ t. This will make the SQL statement easier to maintain, but it will bring about another problem, namely the performance loss. Because the table variables actually use temporary tables, which increases Additional I/O overhead, the table variables are not suitable for large data volumes and frequent queries. Therefore, another solution is provided in SQL Server 2005, which is the common table expression (CTE). Using CTE can make the SQL statement maintainability, CTE is much more efficient than table variables.
The syntax of CTE is as follows:
Copy codeThe Code is as follows:
[WITH <common_table_expression> [, n]
<Common_table_expression >::=
Expression_name [(column_name [, n])]
AS
(CTE_query_definition)

Now we use CTE to solve the above problem. The SQL statement is as follows:
Copy codeThe Code is as follows:
With
Cr
(
Select CountryRegionCode from person. CountryRegion where Name like 'C %'
)
Select * from person. StateProvince where CountryRegionCode in (select * from cr)

Cr is a common table expression, which is similar to a table variable in use, but SQL Server 2005 has different processing methods for common table expressions.
Pay attention to the following points when using CTE:
1. The CTE must be followed by the SQL statements that use the CTE (such as select, insert, and update). Otherwise, the CTE will become invalid. The following SQL statement cannot use CTE normally:
Copy codeThe Code is as follows:
With
Cr
(
Select CountryRegionCode from person. CountryRegion where Name like 'C %'
)
Select * from person. CountryRegion -- remove this SQL statement.
-- The SQL statement using CTE should be followed by the relevant CTE --
Select * from person. StateProvince where CountryRegionCode in (select * from cr)

2. The CTE can be followed by other CTE, but only one with can be used. Multiple CTE are separated by commas (,), as shown in the following SQL statement:
Copy codeThe Code is as follows:
With
Cte1
(
Select * from table1 where name like 'abc %'
),
Cte2
(
Select * from table2 where id> 20
),
Cte3
(
Select * from table3 where price <100
)
Select a. * from cte1 a, cte2 B, cte3 c where a. id = B. id and a. id = c. id

3. if the expression name of the CTE is the same as that of a data table or view, the SQL statement followed by the CTE still uses the CTE. Of course, the following SQL statement uses a data table or view, as shown in the following SQL statement:
Copy codeThe Code is as follows:
-- Table1 is an existing table.
With
Table1
(
Select * from persons where age <30
)
Select * from table1 -- a public table expression named table1 is used.
Select * from table1 -- a data table named table1 is used.

4. The CTE can reference itself, or reference the pre-defined CTE in the same WITH clause. Forward references are not allowed.
5. the following clause cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE
(2) order by (unless TOP clause is specified)
(3)
(4) OPTION clause with query prompt
(5) FOR XML
(6) FOR BROWSE
6. If you use CTE in a statement that is part of the batch processing, the statement before it must end with a semicolon, as shown in the following SQL:
Copy codeThe Code is as follows:
Declare @ s nvarchar (3)
Set @ s = 'C %'
; -- Add points required
With
T_tree
(
Select CountryRegionCode from person. CountryRegion where Name like @ s
)
Select * from person. StateProvince where CountryRegionCode in (select * from t_tree)

In addition to simplifying nested SQL statements, CTE can also be called recursively.

Microsoft introduced CTE (Common Table Expression) from SQl2005 to strengthen T-SQL. This is similar to a non-persistent view.

According to MSDN Introduction

1. The common table expression (CTE) can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE or create view statement. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query.
CTE can be used:
Create a recursive query. For more information, see recursive queries using common table expressions.

You do not need to replace the view when you do not need to use the view normally. That is to say, you do not have to store the definition in the metadata.

Enable grouping by columns derived from scalar nested select statements, or grouping by uncertain functions or functions with external access.


The generated table is referenced multiple times in the same statement.

Using CTE can improve readability and easily maintain complex queries. Queries can be divided into separate blocks, simple blocks, and logical generation blocks. Then, these simple blocks can be used to generate more complex temporary CTE until the final result set is generated. You can define CTE in user-defined routines (such as functions, stored procedures, triggers, or views.
2. A common table expression (CTE) has an important advantage: It can reference itself to create a recursive CTE. Recursive CTE is a public table expression that repeats the initial CTE to return a subset of data until the complete result set is obtained. When a query references recursive CTE, it is called recursive query. Recursive queries are usually used to return layered data, such as displaying an employee or item list scheme in an organizational chart (one or more components of the parent product, and those components may have child components, or other parent-level product components.
The CTE can greatly simplify the Code required to run recursive queries in SELECT, INSERT, UPDATE, DELETE, or create view statements. In earlier versions of SQL Server, recursive queries usually require temporary tables, cursors, and logic to control recursive step flows. For more information about common table expressions, see use common table expressions.

Here is an example:

For ease of description, the invitation month lists a common self-join Table.
The table structure is as follows:
Table Structure
Copy codeThe Code is as follows:
Create table [dbo]. [CategorySelf] (
[PKID] [int] IDENTITY (1, 1) not null,
[C_Name] [nvarchar] (50) not null,
[C_Level] [int] not null,
[C_Code] [nvarchar] (255) NULL,
[C_Parent] [int] not null,
[InsertTime] [datetime] not null,
[InsertUser] [nvarchar] (50) NULL,
[UpdateTime] [datetime] not null,
[UpdateUser] [nvarchar] (50) NULL,
[SortLevel] [int] not null,
[CurrState] [smallint] not null,
[F1] [int] not null,
[F2] [nvarchar] (255) NULL

CONSTRAINT [PK_OBJECTCATEGORYSELF] PRIMARY KEY CLUSTERED
(
[PKID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Insert some test data
Copy codeThe Code is as follows:
Insert
Insert into [CategorySelf] ([C_Name], [C_Level], [C_Code], [C_Parent], [InsertTime], [InsertUser], [UpdateTime], [UpdateUser], [SortLevel], [CurrState], [F1], [F2])
Select 'classification 1', 1, '0', 0, GETDATE (), 'testuser', DATEADD (dd, 1, getdate (), 'crackuser', 13, 0, 1. 'monthly invitation note' union all
Select 'classification 2', 1, '0', 0, GETDATE (), 'testuser', DATEADD (dd, 78, getdate (), 'crackuser', 12, 0, 1. 'monthly invitation note' union all
Select 'category 3', 1, '0', 0, GETDATE (), 'testuser', DATEADD (dd, 6, getdate (), 'crackuser', 10, 0, 1. 'monthly invitation note' union all
Select 'classification 4', 2, '1', 1, GETDATE (), 'testuser', DATEADD (dd, 75, getdate (), 'crackuser', 19,0, 1. 'monthly invitation note' union all
Select 'classification 5', 2, '2', 2, GETDATE (), 'testuser', DATEADD (dd, 3, getdate (), 'crackuser', 17, 0, 1. 'monthly invitation note' union all
Select 'classification 6', 3, '123', 4, GETDATE (), 'testuser', DATEADD (dd, 4, getdate (), 'crackuser', 16, 0, 1. 'monthly invitation note' union all
Select 'classification 7', 3, '123', 4, GETDATE (), 'testuser', DATEADD (dd, 5, getdate (), 'crackuser', 1/4, 1. 'monthly invitation note' union all
Select 'classification 8', 3, '123', 5, GETDATE (), 'testuser', DATEADD (dd, 6, getdate (), 'crackuser', 2/5, 1. 'monthly invitation note' union all
Select 'classification 9', 4, '2014/1/6', 6, GETDATE (), 'testuser', DATEADD (dd, 7, getdate (), 'crackuser ', 5, 0, 1, 'monthly invitation remarks 'union all
Select 'classification 10', 4, '2014/1/6', 6, GETDATE (), 'testuser', DATEADD (dd, 7, getdate (), 'crackuser ',, 0, 1, 'monthly invitation remarks 'union all
Select 'classification 11', 4, '2014/1/6', 6, GETDATE (), 'testuser', DATEADD (dd, 8, getdate (), 'crackuser ',, 0, 1, 'monthly invitation remarks 'union all
Select 'classification 12', 4, '2014/1/8', 8, GETDATE (), 'testuser', DATEADD (dd, 10, getdate (), 'crackuser ', 3, 0, 1, 'monthly invitation remarks 'union all
Select 'classification 13', 4, '2014/1/8', 8, GETDATE (), 'testuser', DATEADD (dd, 15, getdate (), 'crackuser ', 1, 0, 1, 'monthly invitation remarks'

A typical application scenario is: In this self-associated table, the query class with PKID 2 contains all sub-categories. In many cases, we may have to use temporary tables, table variables, cursors, and so on. Now we have the CTE, which is much simpler.
Copy codeThe Code is as follows:
CTEDemo1
WITH SimpleRecursive (C_Name, PKID, C_Code, C_Parent)
AS
(SELECT C_Name, PKID, C_Code, C_Parent FROM CategorySelf where pkid = 2
UNION ALL
SELECT p. C_Name, p. PKID, p. C_Code, p. C_parent
FROM CategorySelf P INNER JOIN
SimpleRecursive a on a. PKID = P. C _ Parent
)
SELECT sr. C_Name as C_Name, c. C_Name as C_ParentName, sr. C_Code as C_ParentCode
FROM SimpleRecursive sr inner join CategorySelf c
On sr. C_Parent = c. PKID

The query result is as follows: C_Name C_ParentName C_ParentCode
Category 5 Category 2 2 2
Category 8 category 5 2/5
Category 12 Category 8 2/5/8
CATEGORY 13 category 8 2/5/8
How do you feel? If I only want to query the second layer, instead of the default unlimited query,
You can add an Option (MAXRECURSION 5) after the preceding SQL statement. Note that layer 5 indicates that the Option is not found at Layer 5th. If you only want to find the second layer, but the actual result has three layers, an error will occur,
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.
In this case, the where condition can be used to solve the problem and avoid errors. See the following SQL statement:
CTEDemo2
Copy codeThe Code is as follows:
WITH SimpleRecursive (C_Name, PKID, C_Code, C_Parent, Sublevel)
AS
(SELECT C_Name, PKID, C_Code, C_Parent, 0 FROM CategorySelf where pkid = 2
UNION ALL
SELECT p. C_Name, p. PKID, p. C_Code, p. C_parent, Sublevel + 1
FROM CategorySelf P INNER JOIN
SimpleRecursive a on a. PKID = P. C _ Parent
)
SELECT sr. C_Name as C_Name, c. C_Name as C_ParentName, sr. C_Code as C_ParentCode
FROM SimpleRecursive sr inner join CategorySelf c
On sr. C_Parent = c. PKID
Where SubLevel <= 2

Query results:
C_Name C_ParentName C_ParentCode
Category 5 Category 2 2 2
Category 8 category 5 2/5
Of course, we do not mean that CTE is omnipotent. A good table design can also solve specific problems to some extent. The following describes how to use regular SQL statements to meet the preceding requirements.
Note: The preceding table has a very important field: C_Code, encoding, format such as "1/2". "2/5/8" indicates that the upper-level category of the classification is 1/2, 2/5/8.
In this way, the query is much simpler. The query uses the PKID as 2 to include all subcategories:
Copy codeThe Code is as follows:
SELECT C_Name as C_Name, (Select top 1 C_Name from CategorySelf s where c. C_Parent = s. PKID) as C_ParentName, C_Code as C_ParentCode
From CategorySelf c where C_Code like '2/%'

The query contains all sub-categories with PKID 2 at a level not greater than 3.
Copy codeThe Code is as follows:
SELECT C_Name as C_Name, (Select top 1 C_Name from CategorySelf s where c. C_Parent = s. PKID) as C_ParentName, C_Code as C_ParentCode
From CategorySelf c where C_Code like '2/% 'and C_Level <= 3

The query result is the same as the preceding one. We can see that, sometimes, a good table structure design is very important.
You are invited to complete the sharing at a.m. on June 23.
Some people are very concerned about performance issues. No tests have been performed. A million-Level Test Report will be attached later. However, two points of understanding about the invitation month are missing:
I. CTE is actually object-oriented, and the basis for running is CLR. A good note is that the With query statement is case sensitive. That is, "C_Code" and "c_Code" are different, and the latter reports an error. This is different from a common SQL statement.
2. This application example focuses on simplifying the business logic. Even if the performance is poor, the traditional processing methods such as temporary tables, table variables, and cursors are simplified or optimized at the business level.

A common table expression (CTE) can be considered as a temporary result set defined within the execution range of a single SELECT, INSERT, UPDATE, DELETE, or create view statement. CTE is similar to a derived table. It is not stored as an object and is only valid during the query. Unlike the derived table, the CTE can be referenced by itself or multiple times in the same query.
CTE can be used:
Create a recursive query.
You do not need to replace the view when you do not need to use the view normally. That is to say, you do not have to store the definition in the metadata.
Enable grouping by columns derived from scalar nested select statements, or grouping by uncertain functions or functions with external access.
The generated table is referenced multiple times in the same statement.
Using CTE can improve readability and easily maintain complex queries. Queries can be divided into separate blocks, simple blocks, and logical generation blocks. Then, these simple blocks can be used to generate more complex temporary CTE until the final result set is generated.
You can define CTE in user-defined routines (such as functions, stored procedures, triggers, or views.
CTE Structure
The CTE consists of the expression name indicating the CTE, the list of optional columns, and the query that defines the CTE. After the CTE is defined, You can reference it in the SELECT, INSERT, UPDATE, or DELETE statement, just like referencing a table or as you would. The CTE can also be used in the create view statement as part of the SELECT statement.
The basic syntax structure of CTE is as follows:
WITH expression_name [(column_name [,... n])]
AS
(CTE_query_definition)
The column name list is optional only when different names are provided for all result columns in the query definition.
The statement for running CTE is:
SELECT <column_list>
FROM expression_name
Example:
Copy codeThe Code is as follows:
With s_name
(
Select s. name, SC. c, SC. grade from SQL Server student AS s, SC
Where s. s # = SC. s #
)
Select * from s_name

Pay attention to the following points when using CTE:
1. The CTE must be followed by the SQL statements that use the CTE (such as select, insert, and update). Otherwise, the CTE will become invalid.
2. The CTE can be followed by other CTE, but only one with can be used. Multiple CTE are separated by commas.
3. if the expression name of the CTE is the same as that of a data table or view, the SQL statement followed by the CTE still uses the CTE. Of course, the following SQL statement uses a data table or view.
4. The CTE can reference itself (recursive query) or the pre-defined CTE in the same WITH clause. Forward references are not allowed.
5. the following clause cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE
(2) order by (unless TOP clause is specified)
(3)
(4) OPTION clause with query prompt
(5) FOR XML
(6) FOR BROWSE
6. If you use CTE in a statement that is part of the batch processing, the statement before it must end with a semicolon.