Common table expressions (CTE)

The common table expression (CTE) is a new solution provided in SQL Server 2005.

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:
(1) A common table expression (CTE) can reference itself to create a recursive CTE.
(2) Replace the view when you do not need to use the view normally. That is to say, you do not have to define the view to be stored in the metadata.
(3) Enable grouping by columns derived from the scalar nested SELECT statement, or grouping by uncertain functions or functions with external access.
(4) The generated table is referenced multiple times in the same statement.
(5) You can define CTE in user-defined routines (such as functions, stored procedures, triggers or views.
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.

CTE Structure
The CTE consists of the expression name indicating the CTE, the list of optional columns, and the query that defines the CET. After the CTE is defined, You can reference it in select, insert, update, or delete statements, just like referencing a table or view. The CTE can also be used in the create view statement as part of the SELECT statement.

CTE syntax structure:

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

From expression_name

Note the following when using CTE:
(1) The CTE must be followed by the SQL statement that uses the CTE. Otherwise, the CTE will become invalid.

With CRS
As (select number from employer where employee like 'd % ')
-- Select * From person-CTE must follow the CTE statement directly. Otherwise, an error is returned.
Select * From depart where employee ID in (select * from CRS)

(2) The CTE can be followed by multiple CTE, but only one with can be used. Multiple CTE are separated by commas.

For example, find the Department and salary of employees whose names start with 'd' and whose ages are not less than the average age.

With
CRS as (select number from employer where employee like 'd % '),
Crs2 as (select AVG (AGE) as average age from employer ),
Crs3 as (select number from employer where age> = (select * From crs2) and number in (select * from CRS ))

Select * From depart where employee ID in (select * From crs3)

(3) the following clause cannot be used in cte_query_definition:

Compute, compute by, order by (unless top clause is specified), into, option clause with query prompt, for XML, for browse

Example:
Create connected tables and insert data

Create Table employer (No. Int, employee char (6), age INT)
-- Insert data
Insert into employer select 1, 'zhang ', 20
Insert into employer select 2, 'lil', 21
Insert into employer select 3, 'wang ', 22
Insert into employer select 4, 'zhao', 23
Insert into employer select 5, 'duany', 24
Insert into employer select 6, 'duany', 25

Create Table depart (Department char (10), employee ID int, salary INT)
-- Insert data
Insert into depart select 'a, 1,100
Insert into depart select 'a, 2,200
Insert into depart select 'a, 3,300
Insert into depart select 'a, 4,400
Insert into depart select 'B', 5,500
Insert into depart select 'B', 6,600

Query the departments and salaries of employees starting with 'D'
(1) nesting
Select *
From depart
Where employee No. In (Select No. from employer where employee like 'd % ')
Order by department, employee ID

Result:

Department employee ID salary

B 5 500
B 6 600

(2) Public expressions

With CRS
As (select number from employer where employee like 'd % ')
Select *
From depart where employee ID in (select * from CRS)

Result:

Department employee ID salary

B 5 500
B 6 600

(3) Table Variables

Declare @ tab table (No. INT)
Insert into @ tab (No)
(Select number from employer where employee like 'd % ')
Select *
From depart where employee ID in (select * From @ tab)

Department employee ID salary

B 5 500
B 6 600