Common table expressions (Common table expression)

Issue: Write a complex SQL statement derived from a statement of the basic Select/from/where type.

Scenario 1: Write a T-SQL query statement that uses a derived table (inline view) within the FROM clause.

Scenario 2: Working with views

Scenario 3: Using a common expression (CTE)

Comparison:

Views: Typically used to decompose large queries without duplicating or storing data in temporary tables, which can be reused in a database.

1 CREATE VIEWVwmyview as2 SELECT3EmployeeID,COUNT(*) asNumorders,MAX(OrderDate) asMaxDate4  fromOrders5 GROUP  byEmployeeID6 GO7 8 SELECT 9E.employeeid, OE. Numorders, OE. MaxDate, E.reportsto asManagerID,Ten om. Numorders, OM. MaxDate One  from  AEmployees ase -     INNER JOINVwmyview asOe onE.employeeid=OE. EmployeeID -     INNER JOINVwmyview asOm onE.reportsto=Om. EmployeeID

Derived tables: You can only access them in the statement where the derived table is located, making the query more difficult to read and maintain (if you want to use derived tables multiple times in the same batch, you must copy and paste derived tables)

1 SELECT 2E.employeeid, OE. Numorders, OE. MaxDate, E.reportsto asManagerID,3 om. Numorders, OM. MaxDate4  from 5Employees ase6     INNER JOIN 7(SELECTEmployeeID,COUNT(*),MAX(OrderDate)8           fromOrders9          GROUP  byEmployeeID) asOE (EmployeeID, numorders, MaxDate)Ten          onE.employeeid=OE. EmployeeID One      Left JOIN  A(SELECTEmployeeID,COUNT(*),MAX(OrderDate) -           fromOrders -          GROUP  byEmployeeID) asom (EmployeeID, numorders, MaxDate) the          onE.reportsto=Om. EmployeeID

CET: Improved readability of T-SQL (like a view), used multiple times in queries immediately following the same batch, without creating temporary or virtual tables internally

1; withEmporderscte (EmployeeID, Numorders, MaxDate) as2 (3   SELECTEmployeeID,COUNT(*),MAX(OrderDate)4    fromOrders5   GROUP  byEmployeeID6 )7 8 SELECT 9 E.employeeid, OE. Numorders, OE. MaxDate,TenE.reportsto asManagerID, OM. Numorders, OM. MaxDate One  from  AEmployees ase -     INNER JOINEMPORDERSCTE OE onE.employeeid=OE. EmployeeID -      Left JOINEmporderscte om onE.reportsto=Om. EmployeeID

Common table expressions (Common table expression)