Summary of the CTE (common table expressions) (Common table expression) in SQL

The with as phrase, also called the subquery section (subquery factoring), allows you to do many things, define a SQL fragment that will be used by the entire SQL statement

A. Meaning of with AS

the with as phrase, also called the subquery section (subquery factoring), allows you to do many things, defining 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.
Especially useful for union all. Because each part of union all may be the same, but if each part goes through it, the cost is too high, so you can use the with as phrase, as long as you execute it again. If the table name defined by the with as phrase is called more than two times, the optimizer automatically places the data obtained from the with as phrase into a temp table, if it is called only once. The hint materialize, however, is to force the data in the with as phrase into a global temporary table. Many queries can improve speed in this way.

two. How to use

Let's look at one of the following nested query statements:

SELECT * FROM person. StateProvince where Countryregioncode in (select Countryregioncode by person. CountryRegion where Name like ' c% ')

The query statement above uses a subquery. Although this SQL statement is not complex, it can make SQL statements very difficult to read and maintain if there are too many nested hierarchies. Therefore, you can also use table variables to solve this problem, the SQL statement 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 Countryregioncodein (SELECT * from @t)

Although the above SQL statement is more complex than the first, it puts the subquery in the table variable @t, which makes the SQL statement easier to maintain, but it also introduces another problem, the loss of performance. Because table variables actually use temporary tables, which increases the additional I/O overhead, table variables do not work well for large data volumes and queries frequently. To do this, another solution is provided in SQL Server 2005, which is a common table expression (CTE) that uses a CTE to make the SQL statement maintainable, while the CTE is much more efficient than a table variable.
Here is the syntax for the CTE:

[With <common_table_expression> [, N]]<common_table_expression>::=expression_name [(column_name [, N]) ]as (cte_query_definition)

Now using the CTE to solve the above problem, the SQL statement is as follows:

WITHCR as (select Countryregioncode from person. CountryRegion where Name like ' c% ') select * from person. StateProvince where Countryregioncode in (SELECT * from CR)

Where CR is a common table expression that is similar to a table variable in use, except that SQL Server 2005 differs in how common table expressions are handled.
The following points should be noted when using a CTE:
1. The CTE must be followed directly with the SQL statement that uses the CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail. The CTE will not work correctly as in the following SQL statement:

WITHCR as (select Countryregioncode from person. CountryRegion where Name like ' c% ') select * from person. CountryRegion-This SQL statement should be removed-the SQL statement using the CTE should be immediately followed by the associated CTE--select * from person. StateProvince where Countryregioncode in (SELECT * from CR)

2. The CTE can also be followed by other CTE, but only one with, the middle of multiple CTE separated by commas (,), as shown in the following SQL statement:

Withcte1 as (SELECT * FROM table1 where name like ' abc% '), Cte2 as (SELECT * from table2 where ID >), cte3 as (SELECT * F Rom Table3 where price < () 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 a data table or view, the SQL statement immediately following the CTE is still using the CTE, and of course, the subsequent SQL statement uses the data table or view, as shown in the following SQL statement:

--Table1 is a physically present table Withtable1 as (SELECT * from persons where age <) SELECT * FROM table1--uses a common table expression named Table1 select * From table1--using a data table named Table1

4. The CTE can refer to itself, or it can refer to a pre-defined CTE in the same with clause. Forward references are not allowed.
5. The following clauses cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE by
(2) ORDER by (unless the TOP clause is specified)
(3) into
(4) OPTION clause with query hint
(5) for XML
(6) for BROWSE
6. If the CTE is used in a statement that is part of a batch, then the statement before it must end with a semicolon, as shown in the following SQL:

declare @s nvarchar (3) Set @s = ' c% '; --You must add a semicolon witht_tree as (select Countryregioncode from person. CountryRegion where Name is @s) select * from person. StateProvince where Countryregioncode in (SELECT * from T_tree)

In addition to simplifying nested SQL statements, the CTE can also make recursive calls.

Microsoft introduced a CTE (Common Table Expression) from SQl2005 to harden T-SQL. This is a good thing like a non-persistent view.

Follow MSDN Introduction

1. A common table expression (CTE) can be thought of as a temporary result set 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 is only valid for the duration of the query. Unlike a derived table, a CTE can be self-referenced and can be referenced more than once in the same query.
The CTE can be used to:
Create a recursive query. For more information, see recursive queries that use common table expressions.

Replace the view when you do not need to use the view normally, that is, you do not have to store the definition in metadata.

Enables grouping of columns derived from scalar nested SELECT statements, or by nondeterministic functions or functions that have external access.


The resulting table is referenced more than once in the same statement.

Using a CTE gives you the benefit of improved readability and easy maintenance of complex queries. 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. You can define a CTE in a user-defined routine, such as a function, stored procedure, trigger, or view.
2. The common table expression (CTE) has an important advantage in being able to refer to itself, thus creating a recursive CTE. A recursive CTE is a common table expression that repeats an initial CTE to return a subset of data until the full result set is obtained. When a query references a recursive CTE, it is referred to as a recursive query. Recursive queries are typically used to return hierarchical data, for example, to display data from an employee or BOM scenario in an organization chart where the parent product has one or more components, and those components may have subcomponents, or components of other parent products.
Recursive CTE can greatly simplify the code required to run a recursive query in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, recursive queries often required the use of temporal tables, cursors, and logic to control the flow of recursive steps. For more information about common table expressions, see Working with common table expressions.

Here are some examples:

In order to describe the convenience, invited the month specially lists a common self-related table
The table structure is as follows:
Table structure

CREATE TABLE [dbo]. [Categoryself] ([PKID] [int] IDENTITY () not null,[c_name] [nvarchar] (no null,[c_level] [int] not null,[c_code] [nvarchar] (255) NU Ll,[c_parent] [int] not null,[inserttime] [datetime] not null,[insertuser] [nvarchar] (a) null,[updatetime] [datetime] Not null,[updateuser] [nvarchar] (a) null,[sortlevel] [int] not null,[currstate] [smallint] not NULL,[F1] [int.] Not null,[ F2] [nvarchar] (255) nullconstraint [pk_objectcategoryself] PRIMARY KEY CLUSTERED ([PKID] ASC) with (Pad_index = OFF, statis Tics_norecompute = off, Ignore_dup_key = off, Allow_row_locks = on, allow_page_locks = on) on [PRIMARY]) on [Primary]go

and insert some test data.

Insertinsert into [categoryself] ([C_name],[c_level], [c_code],[c_parent], [Inserttime], [Insertuser], [UpdateTime], [ UpdateUser], [Sortlevel], [currstate], [F1], [F2]) SELECT ' Category 1 ', 1, ' 0 ', 0,getdate (), ' TestUser ', DATEADD (Dd,1,getdate ()) , ' Crackuser ', 13,0,1, ' Invite month notes ' Union allselect ' Category 2 ', 1, ' 0 ', 0,getdate (), ' TestUser ', DATEADD (Dd,78,getdate ()), ' Crackuser ', 12,0,1, ' Invitation Month notes ' Union allselect ' Category 3 ', 1, ' 0 ', 0,getdate (), ' TestUser ', DATEADD (Dd,6,getdate ()), ' Crackuser ', 10,0,1, ' Invitation Month notes ' Union allselect ' Category 4 ', 2, ' 1 ', 1,getdate (), ' TestUser ', DATEADD (Dd,75,getdate ()), ' Crackuser ', 19,0,1, ' Invite month notes ' union Allselect ' Classification 5 ', 2, ' 2 ', 2,getdate (), ' TestUser ', DATEADD (Dd,3,getdate ()), ' Crackuser ', 17,0,1, ' Invite month notes ' Union Allselect ' Categories 6 ', 3, ' 4,getdate ', ' testUser ', ' DATEADD ' (Dd,4,getdate ()), ' Crackuser ', 16,0,1, ' Invite month notes ' Union allselect ' category 7 ', 3, ' 1/ 4 ', 4,getdate (), ' TestUser ', DATEADD (Dd,5,getdate ()), ' Crackuser ', 4,0,1, ' Invite month notes ' Union allselect ' category 8 ', 3, ' 2/5 ', 5, GETDATE (), ' TestUser ', DATEADD (Dd,6,getdate ()), ' Crackuser ', 3,0,1, ' Invite month notes ' Union allselect ' category 9 ', 4, ' 1/4/6 ', 6,getdate (), ' TestUser ', DATEADD (Dd,7,getdate ()), ' Crackuser ', 5,0,1, ' Invite month notes ' Union allselect ' category 10 ', 4, ' 1/4/6 ', 6, GETDATE (), ' TestUser ', DATEADD (Dd,7,getdate ()), ' Crackuser ', 63,0,1, ' Invite month notes ' Union allselect ' category One ', 4, ' 1/4/6 ', 6,getdate (), ' TestUser ', DATEADD (Dd,8,getdate ()), ' Crackuser ', 83,0,1, ' Invite month notes ' Union Allselect ' classification of ', 4, ' 2/5/8 ', 8,getdate (), ' TestUser ', DATEADD (Dd,10,getdate ()), ' Crackuser ', 3,0,1, ' Invite month notes ' Union Allselect ' classification of ', 4, ' 2/5/8 ', 8,getdate (), ' TestUser ', DATEADD (Dd,15,getdate ()), ' Crackuser ', 1,0,1, ' Invite month notes '

A typical scenario is: in this self-correlating table, a query with a pkid of 2 contains all subcategories. Perhaps in many cases, we have to use temporary tables/table variables/cursors and so on. Now that we have a CTE, it's a lot easier.

Ctedemo1with simplerecursive (C_name, PKID, C_code,c_parent) as (SELECT c_name, PKID, c_code,c_parent from Categoryself WHERE PKID = 2UNION allselect p.c_name, P.pkid, P.c_code,p.c_parentfrom categoryself p INNER joinsimplerecursive A on A.PK ID = p.c_parent) SELECT Sr. C_name as C_name, C.c_name as C_PARENTNAME,SR. C_code as C_parentcodefrom simplerecursive sr INNER join Categoryself Con Sr. C_parent=c.pkid

The query results are as follows: C_name c_parentname C_parentcode
Category 5 Category 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 tier, rather than the default infinite query down,
You can add an option (Maxrecursion 5) to the SQL above, and note that 5 indicates that you are not looking down to the 5th level. If you only want to find the second tier, but the actual result has three layers, this error occurs,
MSG 530, Level A, State 1, line 1
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.
This can be solved by the where condition, and the guarantee is not error, see the following SQL statement:
CTEDemo2

With Simplerecursive (C_name, PKID, C_code,c_parent,sublevel) as (SELECT c_name, PKID, c_code,c_parent,0 from Categoryself WHERE PKID = 2UNION allselect p.c_name, P.pkid, P.c_code,p.c_parent,sublevel+1from categoryself p INNER JOINS Implerecursive 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_parentcodefrom simplerecursive sr INNER join Categoryself Con Sr. C_parent=c.pkidwhere sublevel<=2

Query Result:
C_name C_parentname C_parentcode
Category 5 Category 2 2
Category 8 Category 5 2/5
Of course, we are not saying that the CTE is omnipotent. A good table design can also solve a particular problem in some way. The above requirements are implemented with regular SQL.
Note: It is important to have a field in the table above, which is c_code, encoded, formatted like "1/2", "2/5/8" indicates that the upper class of the classification is 1/2,2/5/8
In this way, we have a much simpler query, and the query with a pkid of 2 contains all sub-categories:

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_par Entcodefrom categoryself C where c_code like ' 2/% '

Queries with Pkid of 2 contain all subcategories, and the level is not greater than 3

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_par Entcodefrom categoryself C where c_code like ' 2/% ' and c_level<=3

The results of the query are omitted. Here we see that sometimes good table structure design is very important.
Invite the month to complete the sharing on 2009.10.23 1:36.
Some people are concerned about performance issues. No tests have been done at this time. A millions test report will be attached later. However, a two-point understanding invites the month to forget to add:
First, the CTE is actually object-oriented and runs on the basis of the CLR. A good explanation is that the WITH query statement is case sensitive to the field. That is, "C_code" and "C_code" are not the same, the latter will be an error. This is different from normal SQL statements.
Second, this application example is to simplify the business logic, even if the performance is poor, but the temporal table/table variables/cursors and other traditional processing is a business level of simplification or optimization.

A common table expression (CTE) can be thought of as a temporary result set 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 is only valid for the duration of the query. Unlike a derived table, a CTE can be self-referenced and can be referenced more than once in the same query.
The CTE can be used to:
Create a recursive query.
Replace the view when you do not need to use the view normally, that is, you do not have to store the definition in metadata.
Enables grouping of columns derived from scalar nested SELECT statements, or by nondeterministic functions or functions that have external access.
The resulting table is referenced more than once in the same statement.
Using a CTE gives you the benefit of improved readability and easy maintenance of complex queries. 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.
You can define a CTE in a user-defined routine, such as a function, stored procedure, trigger, or view.
Structure of the CTE
A CTE consists of an expression name that represents a CTE, an optional list of columns, and a query that defines a CTE. After you define a CTE, you can reference it in a SELECT, INSERT, UPDATE, or DELETE statement just as you would reference a table or view. The CTE can also be used with the CREATE VIEW statement as part of defining a SELECT statement.
The basic syntax structure of a CTE is as follows:
With Expression_name [(column_name [,... n])]
As
(cte_query_definition)
The column Name list is optional only if you provide a different name for all the result columns in the query definition.
The statement that runs the CTE is:
SELECT <column_list>
From Expression_name
Example:

With S_name as (select S.name, sc.c,sc.grade from SQL Server student as S,scwhere s.s#=sc.s#) SELECT * FROM S_name

The following points should be noted when using a CTE:
1. The CTE must be followed directly with the SQL statement that uses the CTE (such as SELECT, Insert, UPDATE, and so on), otherwise the CTE will fail.
2. The CTE can also be followed by other CTE, but only one with, the middle of multiple CTE separated by commas (,).
3. If the expression name of the CTE is the same as a data table or view, the SQL statement immediately following the CTE is still using the CTE, and of course, the subsequent SQL statement uses the data table or view.
4. The CTE can refer to itself (recursive query), or it can refer to a pre-defined CTE in the same with clause. Forward references are not allowed.
5. The following clauses cannot be used in CTE_query_definition:
(1) COMPUTE or COMPUTE by
(2) ORDER by (unless the TOP clause is specified)
(3) into
(4) OPTION clause with query hint
(5) for XML
(6) for BROWSE
6. If the CTE is used in a statement that is part of a batch, the statement before it must end with a semicolon.

Summary of the CTE (common table expressions) (Common table expression) in SQL