Sql server® 2012 t-sql fundamentals

 

CHAPTER 5

 

Table Expressions

 

 163

This query joins two instances of a table expression to create two derived tables: The first derived 

table, Cur, represents current years, and the second derived table, Prv, represents previous years. The 

join condition Cur.orderyear = Prv.orderyear + 1 ensures that each row from the first derived table 

matches with the previous year of the second. Because the code makes the join a LEFT outer join, 

the first year that has no previous year is also returned from the Cur table. The SELECT clause of the 

outer query calculates the difference between the number of customers handled in the current and 

previous years.

The code in Listing 5-3 produces the following output.

orderyear   curnumcusts prvnumcusts growth 

----------- ----------- ----------- ----------- 

2006        67          NULL        NULL 

2007        86          67          19 

2008        81          86          –5

The fact that you cannot refer to multiple instances of the same derived table forces you to main-

tain multiple copies of the same query definition. This leads to lengthy code that is hard to maintain 

and is prone to errors.