TSQL -> Common Table Expressions (CTE) in TSQL

Common Table Expressions was introduced in TSQL (version 2005). Common Table Expressions (CTE) are part of ANSI SQL 99, or SQL3. CTE provides alternative syntax for mastering nested queries, and allso can be used for writing recursive queries.


/* In the below example we define CTE by name "a". CTE statement retrives sales statistics for all items in year 1999. The CTE is then followed by a select calling it, that displayes results from "a" adding to every itemcode item title */

WITH a AS
(
   SELECT t1.ItemCode, SUM(t1.LineTotal) AS Sale1999,
   SUM(t1.Qnt) AS Qnt1999
   FROM dbo.InvLines AS t1 INNER JOIN dbo.Invoice AS t2
   ON t1.DocKey = t2.DocKey
   WHERE Year(t2.DocDate)=1999
   GROUP BY t1.ItemCode
)
SELECT a.ItemCode, b.ItemName, a.Sale1999, a.Qnt1999
FROM a INNER JOIN dbo.Items AS b
ON a.ItemCode = b.ItemCode;

/* the same effect can be achieved by using well known nested-query syntax */

SELECT a.ItemCode, b.ItemName, a.Sale1999, a.Qnt1999
FROM
   (SELECT t1.ItemCode, SUM(t1.LineTotal) AS Sale1999,
    SUM(t1.Qnt) AS Qnt1999
    FROM dbo.InvLines AS t1 INNER JOIN dbo.Invoice AS t2
    ON t1.DocKey = t2.DocKey
    WHERE Year(t2.DocDate)=1999
    GROUP BY t1.ItemCode) AS a
INNER JOIN dbo.Items AS b
ON a.ItemCode = b.ItemCode;

/* but CTE advocates says that it is easier to code and review nested queries in CTE way */



Query results will came as following:
ItemCodeItemNameSale1999Qnt1999
0036876Memorex 8.5Gb/ 2.4x DVD+R Dual Layer ..23200232
5003258Philips DVP642 DivX-Certified ..12226.2426
77003Panasonic DMR-ES15S DVD Recorder ..800064
........


sqlexamples.info