Introduction to the use of with as in SQL Server

Time:2020-9-25

1、 The meaning of with as

With as phrase, also known as subquery factoring, allows you to do a lot of things and 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 the data providing part.
Especially useful for union all. Because each part of union all may be the same, but if each part is executed once, the cost will be too high. Therefore, the phrase with as can be used, and it can only be executed once. If the table name defined by with as phrase is called more than twice, the optimizer will automatically put the data obtained by with as phrase into a temp table. If it is called only once, it will not. The prompt materialize forces the data in the with as phrase into a global temporary table. Many queries can be speeded up in this way.

2、 How to use it

Let’s look at the following nested query statement:

select * from person.StateProvince where CountryRegionCode in
         (select CountryRegionCode from person.CountryRegion where Name like ‘C%’)

The above query statement uses a subquery. Although this SQL statement is not complex, it will be very difficult to read and maintain if there are too many nested levels. 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 method, the subquery is placed in the table variable @ T. This will make the SQL statement easier to maintain, but it will bring another problem, that is, the loss of performance. Because the table variables actually use temporary tables, which increases the additional I / O overhead, the way of table variables is not suitable for large amount of data and frequent queries. Therefore, another solution is provided in SQL Server 2005, which is common table expression (CTE). Using CTE can make SQL statements maintainable. At the same time, CTE is much more efficient than table variables.

Here is the syntax of CTE:

[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
        expression_name [ ( column_name [ ,n ] ) ]
    AS
        ( CTE_query_definition )

Now use 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)

Cr is a common table expression, which is similar to table variables in use, except that SQL Server 2005 is different in the way it handles common table expressions.

The following points should be paid attention to when using CTE

1. CTE must be followed by SQL statements (such as select, insert, update, etc.) that use CTE. Otherwise, CTE will be invalid. For example, the following SQL statement will not work normally

with
			cr as
			(
			select CountryRegionCode from person.CountryRegion where Name like 'C%'
			)
			select * from person.CountryRegion 
			--This SQL statement should be removed
			--SQL statements using CTE should be immediately followed by the relevant CTE--
	select * from person.StateProvince where CountryRegionCode in (select * from cr)

2. CTE can be followed by other CTEs, but only one with can be used. Multiple CTEs are 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 CTE is the same as a data table or view, the SQL statement immediately following the CTE still uses CTE. Of course, the SQL statement following the CTE uses the data table or view, as shown in the following SQL statement:

–Table1 is an actual table

with
table1 as
(
    select * from persons where age < 30
)
select * from table1
--A common table expression named table1 is used
select * from table1
--A data table named table1 was used

4. CTE can refer to itself or CTE defined in the same with clause. Forward references are not allowed.

5. Not in CTE_ query_ The following clauses are used in the 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 CTE is used in a statement that is part of a batch, the statement before it must end with a semicolon, as shown in the following SQL:

declare @s nvarchar(3)
			set @s = 'C%'
			; -- semicolon is required
			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, CTE can also make recursive calls, which will be covered in the next article.

First look at the following data table (t_ tree):

The figure above shows the data in a table with three fields: ID and node_ name、parent_ id。 In fact, there is a tree structure in this table, which is divided into three levels: Province, city and district. Where id represents the ID number and node of the current province, city or district_ Name means name and parent_ ID represents the ID of the parent node of the node.
Now there is a requirement to query all the cities and districts under a province (the query results include provinces). If only using SQL statements to achieve, you need to use cursors, temporary tables and other technologies. However, CTE can also be used in SQL Server 2005.

From the perspective of this requirement, it belongs to recursive call, that is to say, first find out the records of provinces that meet the price adjustment. In this example, the records of “Liaoning Province” are as follows:

id   node_name   parent_id

1. Liaoning Province

Then check all parent_ Records with ID field value 1 are as follows:

id   node_name   parent_id

2. Shenyang 1

3. Dalian City

Finally, check the parent again_ Records with ID field value of 2 or 3 are as follows:

id    node_name    parent_id

4. Dadong District 2

5. Shenhe District 2

6 Tiexi District 2

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

The above query process can also be understood according to the recursive process, that is, first check the record of the specified province (Liaoning Province). After obtaining the record, it will have the corresponding ID value, and then enter the recursive process, as shown in the figure below.

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

    resultset(n) = resultset(n-1) union all current_resultset

Where resultset (n) is the final result set and resultset (n – 1) is the penultimate result set, current_ Resultset represents the result set currently found, and the record set of “Liaoning Province” at the beginning is equivalent to the initial condition of recursion. The end condition of recursion is current_ Set 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 (record set containing province)
			Save the ID of the result set in the collection
			getResultSet(current_resultset)
			}
			current_ Resultset = find out the current result set according to the ID value in the ID set
			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 relatively complex to implement, but CTE provides us with a simple syntax to simplify this process.
The CTE syntax for recursion is as follows:

[ WITH <common_table_expression> [ ,n ] ]
<common_table_expression>::=
         expression_name [ ( column_name [ ,n ] ) ]
    AS (
        CTE_ query_ Definition1 — anchor member (that is, the initial value or the first result set)
       union all
        CTE_ query_ Definition2 — recursive member
     )

SQL statement

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 parent based on the ID value obtained from the previous query result set_ ID     
			--Then district changes the current query result set and continues to execute the following select statement
			--If the result set is not null, it is merged with the final query result, and the final query is updated with the merged result
			--Query the result; otherwise, stop execution. Finally, the result set of district 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 “Liaoning Province” and “Shenyang City” have sub nodes.

When defining and using recursive CTE, we should pay attention to the following points:

1. Recursive CTE definition must contain at least two CTE query definitions, one anchor member and one recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must precede the first recursive member definition. All CTE query definitions are anchor members, except when they refer to the CTE itself.
2. Anchor members must be used in combination 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 combining multiple recursive members.
3. The number of columns in anchor member and recursive member must be the same.
4. The data type of column in recursive member must be consistent with that of corresponding column in anchor member.
5. The from clause of recursive member can only refer to CTE expression once_ name。
6. CTE of recursive member_ query_ The following items are not allowed in definition:

(1)SELECT DISTINCT
(2)GROUP BY
(3)HAVING
(4) Scalar aggregation
(5)TOP
(6) Left, right, outer join (inner join allowed)
(7) Subquery
(8) Apply to CTE_ query_ Prompt for recursive reference of CTE in definition.

7. All columns returned by recursive CTE can be null no matter how null the columns returned by the participating select statement are.
8. If the recursive CTE combination is not correct, it may lead to infinite loop. For example, if a recursive member query definition returns the same values for the parent and child columns, it can cause an infinite loop. You can use the maxrecursion prompt and a value between 0 and 32767 in the option clause of an insert, update, delete, or select statement to limit the number of recursion allowed for a particular statement to prevent infinite loops. This allows you to control the execution of the statement before solving the code problem that generates the loop. The server wide default is 100. If 0 is specified, there is no restriction. Only one maxrecursion value can be specified per statement.
9. You cannot use views that contain recursive common table expressions to update data.
10. You can use CTE to define cursors on queries. Recursive CTE allows only fast forward only cursors and static (snapshot) cursors. If a different cursor type is specified in the recursive CTE, the type is converted to a static cursor type.
11. Tables in remote servers can be referenced in 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.

Here are some supplements, a lot of reference value

With as phrase, also known as subquery factoring
It allows you to do a lot of things and define a SQL fragment that will be used by the entire SQL statement.

As part of providing data.

Code example:


			with temp as
			(select ID, Type_Name, Type_ID
			from T_Base_GoodsType as t
			where t.Shop_ID = @shop_id
			and Type_ID = @Goods_TypeID
			union all
			select t1.ID, t1.Type_Name, t1.Type_ID
			from T_Base_GoodsType as t1
			inner join temp
			on t1.ParentType_ID = temp.Type_ID
			where t1.Shop_ID = @shop_id)
			select *
			from (select Stock_Amount,
			S.StockWarn_Amount,
			S.All_Amount,
			G.Goods_ID,
			G.Goods_Name,
			G.Goods_Unit,
			ROW_NUMBER() over(order by Stock_Amount desc) as rowid
			from T_IM_StockInfo as S
			inner join T_Base_GoodsInfo AS G
			on S.Goods_ID = G.Goods_ID
			inner join temp
			on temp.Type_ID = G.Goods_TypeID
			where S.Shop_ID = @shop_id
			AND G.Shop_ID = @shop_id
			and G.Goods_TypeID = temp.Type_ID
			group by S.Stock_Amount,
			S.All_Amount,
			G.Goods_ID,
			G.Goods_Name,
			G.Goods_Unit,
			S.StockWarn_Amount
			HAVING SUM(S.Stock_Amount) < S.StockWarn_Amount) m
	WHERE rowid between @pageindex and @pagesize

SQL loop (with as phrase is also called subquery part)

--Table structure 
			SELECT id,position,Parentid FROM op_client_sales_structure 

			WITH TEST_CTE
			AS
			(
			SELECT id,position,Parentid,Cast(Parentid AS NVARCHAR(4000)) AS PATH
			FROM op_client_sales_structure team
			WHERE Parentid !=-1
			UNION ALL
			SELECT a.id,a.position,a.Parentid,
			CTE.PATH+','+Cast(a.Parentid AS NVARCHAR(4000)) AS PATH
			FROM op_client_sales_structure a
			INNER JOIN TEST_CTE CTE ON a.id=CTE.Parentid
			)
			SELECT * FROM TEST_CTE WHERE Parentid=(SELECT id FROM op_client_sales_structure WHERE Parentid=-1)
			--Limit recursion times
	OPTION(MAXRECURSION 10)

This article introduces here, hoping to help you.

Recommended Today

Details of LSB steganography

I gave you training last weekendlsbHowever, due to the lack of time, speaking may be too fast, leading to some students can not understand, so this article is made to help students understand!         First of all, let’s talk about bitmap     We might as well enlarge the picture of guanxi […]