In SQL Server, with as introduction

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 from 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 Countryregioncode
In (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:


With
CR 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:


With
CR 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 behind the relevant 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:


With
Cte1 as
(
SELECT * FROM table1 where name like ' abc% '
),
Cte2 as
(
SELECT * from table2 where ID > 20
),
Cte3 as
(
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 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 table that actually exists

With
Table1 AS
(
SELECT * from persons where age < 30
)
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% '
; --Must add a semicolon
With
T_tree as
(
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, the CTE can also make recursive calls, and the contents of this section are described in the next article.

Let's look at one of the following data tables (T_tree):

Shows the data in a table that has three fields: ID, Node_name, parent_id. In fact, this table holds a tree structure, divided into three layers: province, city, and district. Where ID represents the ID number of the current province, city, or region, Node_name represents the name, and parent_id represents the ID of the node's parent node.
Now there is a need to query out all the cities and districts Below a province (the query result contains a province). If you use only SQL statements, you need to use techniques such as cursors, temporal tables, and so on. However, you can also use a CTE in SQL Server2005.

From this point of view belongs to recursive call, that is to find the record of the province to meet the price adjustment, in this case to check the "Liaoning province" record, as follows:

ID Node_name parent_id

1 Liaoning Province 0

Then check all records with the parent_id field value of 1, as follows:

ID Node_name parent_id

2 Shenyang, 1

3 Dalian City 1

Finally, check the records for the parent_id field value of 2 or 3, as follows:

ID Node_name parent_id

4 Dadong 2

5 Shenhe District 2

6 Tiexi District 2

Merging the above three result sets is the final result set.

The above query process can also be interpreted according to the process of recursion, that is, the record of the designated province (Liaoning province), the record is obtained, the corresponding ID value, and then entered the recursive process, as shown in.



As can be seen from the above, the recursive process is the process of merging query result sets with union all, which is equivalent to the following recursive formula:

ResultSet (n) = ResultSet (n-1) UNION ALL Current_resultset

where ResultSet (n) represents the final result set, ResultSet (N-1) represents the second-to-last result set, Current_resultset represents the result set currently found, and the recordset that initially queries "Liaoning Province" is equivalent to the initial conditions of recursion. The end condition of recursion is that Current_resultset is empty. Here is the pseudo-code for this recursive procedure:


Public resultset Getresultset (resultset)
{
if (resultset is null)
{
Current_resultset = First result set (contains a recordset for a province)
Save the ID of the result set in the collection
Getresultset (Current_resultset)
}
Current_resultset = Isolate The current result set based on the ID value in the ID collection
if (Current_result is null) return resultset
Saves the ID of the current result set in the collection
Return Getresultset (ResultSet Union ALL Current_resultset)
}

Get the final result set
ResultSet = Getresultset (null)


As can be seen from the above process, this recursive process is more complex, but the CTE provides us with a simple syntax to simplify the process.
The CTE syntax for implementing recursion is as follows:



[With <common_table_expression> [, N]]
<common_table_expression>::=
Expression_name [(column_name [, N])]
As (
Cte_query_definition1--anchor member (i.e., initial value or first result set)
UNION ALL
Cte_query_definition2--Recursive members
)





With
District AS
(
--Get the first result set and update the final result set
SELECT * from T_tree where Node_name= N ' Liaoning province '
UNION ALL
--The following SELECT statement first queries the PARENT_ID based on the ID value obtained from the previous query result set
--The value of the field, and then district will change the current query result set and continue executing the following SELECT statement
--If the result set is not NULL, merge with the final query result, and update the final check with the results of the merge
--Poll the results; otherwise stop execution. The final district result set is the final result set.
Select A.* from T_tree A, District B
where a.parent_id = b.ID
)
Select * FROM District






With
District AS
(
SELECT * from T_tree where Node_name= N ' Liaoning province '
UNION ALL
Select A.* from T_tree A, District B
where a.parent_id = b.ID
),
District1 as
(
Select A.* from District a where a.id in (select parent_id from District)
)
SELECT * FROM District1






Note: Only the "Liaoning Province" and "Shenyang City" have the following nodes.

The following points should be noted when defining and using recursive CTE:

1. The recursive CTE definition must contain at least two CTE query definitions, one anchor member and one recursive member. You can define multiple anchor members and recursive members, but you must place all of the anchor member query definitions before the first recursive member definition. All CTE query definitions are anchor members, except when they refer to the CTE itself.
2. The anchor member must be used in conjunction with one of the following set operators: Union ALL, Union, INTERSECT, or EXCEPT. Only the UNION all set operator can be used between the last anchor member and the first recursive member, and when multiple recursive members are combined.
3. The number of columns in the anchor member and the recursive member must be the same.
4. The data type of the column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
5. The FROM clause of a recursive member can only refer to the CTE expression_name once.
6. The following items are not allowed in the cte_query_definition of the recursive member:

(1) SELECT DISTINCT

(2) GROUP by

(3) having

(4) Scalar aggregation

(5) TOP

(6) left, right, OUTER join (allows INNER join to appear)

(7) Sub-query

(8) A hint that applies to recursive references to the CTE in CTE_query_definition.

7. Regardless of the nullability of the column returned by the participating SELECT statement, all columns returned by the recursive CTE can be empty.
8. If the recursive CTE combination is incorrect, an infinite loop may result. For example, if the recursive member query definition returns the same value for the parent Arrays parent column, an infinite loop is created. You can limit the number of recursive levels allowed by a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement to prevent an infinite loop. This allows the execution of the statement to be controlled before the code problem that generated the loop is resolved. The server-wide default value is 100. If you specify 0, there is no limit. You can specify only one Maxrecursion value per statement.
9. You cannot update data by using a view that contains a recursive common table expression.
10. You can use a CTE to define cursors on a query. Recursive CTE allows only fast forward-only cursors and static (snapshot) cursors to be used. If a different cursor type is specified in the recursive CTE, the type is converted to a static cursor type.
11. Tables in the remote server can be referenced in the CTE. If a remote server is referenced in a recursive member of the CTE, a spool is created for each remote table so that the tables can be accessed repeatedly locally.

In SQL Server, with as introduction