Shulou(Shulou.com)05/31 Report--

This article mainly introduces "detailed explanation of the usage of sql sentence with as". In daily operation, I believe that many people have doubts about the detailed explanation of the use of sql sentence with as. The editor has consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "detailed explanation of the usage of sql sentence with as"! Next, please follow the editor to study!

1. The meaning of WITH AS the WITH AS phrase, also known as the subquery part (subquery factoring), allows you to do a lot of things, define a SQL fragment, the SQL fragment 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 the UNION ALL as part of providing data. It is especially useful for UNION ALL. Because each part of the 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, just 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 an TEMP table, but not if it is called only once. The prompt materialize forces the data in the WITH AS phrase to be placed in a global temporary table. Many queries can be improved in this way.

2. Take a look at the following nested query statement: copy the code as follows: 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, if there are too many levels of nesting, it will make the SQL statement very difficult to read and maintain. Therefore, you can also use table variables to solve this problem. The SQL statement is as follows: copy the code 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 approach, it puts the subquery in the table variable @ t, which makes the SQL statement easier to maintain, but creates another problem, which is a loss of performance. Because the table variables actually use temporary tables, which adds additional I _ map O overhead, the table variable approach is not very suitable for situations with large amounts of data and frequent queries. To this end, another solution is provided in SQL Server 2005, which is the common table expression (CTE). Using CTE, you can make SQL statements maintainable, and CTE is much more efficient than table variables. The following is the syntax of CTE: the copy code is as follows: [WITH [, n]]:: = expression_name [(column_name [, n])] AS (CTE_query_definition)

Now use CTE to solve the above problem. The SQL statement is as follows: copy the code 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 in use to table variables, except that SQL Server 2005 differs in the way it handles common table expressions. The following points should be noted when using CTE:

1. CTE must be followed directly by SQL statements using CTE (such as select, insert, update, etc.), otherwise, CTE will become invalid. Such as the following SQL statement will not work properly with CTE: copy the code as follows: 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 CTE should be immediately following the relevant CTE-- select * from person.StateProvince where CountryRegionCode in (select * from cr)

2. CTE can also be followed by other CTE, but only one with can be used. Multiple CTE can be separated by commas (,), as shown in the following SQL statement: copy the code as follows: withcte1 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 that of a data table or view, the SQL statement immediately following that CTE still uses CTE. Of course, the following SQL statement uses the data table or view. As shown in the following SQL statement: the copy code is as follows:-- table1 is an actual table withtable1 as (select * from persons where age < 30) select * from table1-- using a common table expression select * from table1 named table1-- using a data table named table1

4. The CTE can refer to itself or to a predefined 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 BY (2) ORDER BY (unless the TOP clause is specified) (3) INTO (4) OPTION clause with query hint (5) FOR XML (6) FOR BROWSE6. 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: copy the code as follows: declare @ s nvarchar (3) set @ s ='C% from person.StateProvince where CountryRegionCode in;-- you must add a semicolon witht_tree as (select CountryRegionCode from person.CountryRegion where Name like @ s) select * from person.StateProvince where CountryRegionCode in (select * from t_tree)

At this point, the study of "detailed explanation of the usage of the sql sentence with as" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.