Table Expressions in SQL server

Table Expressions are sub-queries where a table is expected. There are two types of table expressions. This post explains how to use the Table expressions and their advantages.

• Derived tables
• Common Table Expressions

Derived Tables

A Derived table is a table expression that appears in the FROM clause of a query. Derived tables can be used when the use of column aliases is not possible because another clause is processed before the alias name.

Example:

   1: USE AdventureWorks

   2: SELECT MONTH(HireDate) as Hire_Month

   3: FROM HumanResources.Employee

   4: GROUP BY Hire_Month;

When we execute above query, we will get the following result

Msg 207, Level 16, State 1, Line 4
Invalid column name 'Hire_Month'.

   1: USE AdventureWorks

   2: SELECT Hire_Month

   3: FROM (SELECT MONTH(HireDate) as Hire_Month

   4: FROM HumanResources.Employee) AS m

   5: GROUP BY Hire_Month;

The result of a table expression is always a table or expression.

Example:

   1: SELECT d.StartDate, (SELECT EmployeeID

   2: FROM HumanResources.Employee e WHERE e.EmployeeID = d.EmployeeID)

   3:  AS EmployeeID

   4: FROM HumanResources.EmployeeDepartmentHistory d

   5: WHERE d.StartDate IN ('1998-01-11 00:00:00.000', 

   6: '1997-02-26 00:00:00.000');

Common Table Expressions

A common table expression (CTE) is a named table expression supported by Transact-SQL.  It is similar to Derived table but it is not stored as an object and lasts for the duration of the query.

Common Table Expressions can be used in two types of queries

1. Non-Recursive
2. Recursive

CTEs and Non-Recursive Queries

Non-Recursive form of CTEs can be used as an alternative to Derived Tables and Views. Generally CTE’s are defined using the WITH statement.

Example: 

To find the total dues whose values are greater than the average of all dues and whose freights are greater than 40% of the avg of all dues.

   1: USE AdventureWorks;

   2: SELECT SalesOrderID

   3: FROM Sales.SalesOrderHeader

   4: WHERE TotalDue > (SELECT AVG(TotalDue)

   5: FROM Sales.SalesOrderHeader

   6: WHERE YEAR(OrderDate) = '2002')

   7: AND Freight > (SELECT AVG(TotalDue)

   8: FROM Sales.SalesOrderHeader

   9: WHERE YEAR(OrderDate) = '2002')/2.5;

The main problem with above query is  space consuming and inner query has written twice. We can shorten the syntax of the query and better use CTE to    re-write the above query.

Syntax:

WITH cte_name(column_list) AS (inner_query)

outer_query.

   1: USE AdventureWorks;

   2: WITH price_calc(year_2002) AS

   3: (SELECT AVG(TotalDue)

   4: FROM Sales.SalesOrderHeader

   5: WHERE YEAR(OrderDate) = '2002')

   6: SELECT SalesOrderID

   7: FROM Sales.SalesOrderHeader

   8: WHERE TotalDue > (SELECT year_2002 FROM price_calc)

   9: AND Freight > (SELECT year_2002 FROM price_calc)/2.5;

The CTE name in above query is price_calc and has column named year_2002

The defined CTE name in inner query can be used in outer query.

CTEs and Recursive Queries

We can use CTEs to implement recursion, because CTEs contains reference to themselves.

Syntax:

  WITH cte_name (column_list) AS

  (anchor_member

UNION ALL

recursive_member)

outer_query

With clause contains the two queries that are connected with UNION ALL operator. The first query will be invoked only once, and it holds the result of the recursion. The recursive_member uses the result of first query. After that, system repeatedly invokes the recursive part. The invocation of the recursive member ends when the result of the previous invocation is an empty set.

Example:

   1: WITH list_of_parts(assembly1, quantity, cost) AS

   2: (SELECT containing_assembly, quantity_contained, unit_cost

   3: FROM airplane

   4: WHERE contained_assembly IS NULL

   5: UNION ALL

   6: SELECT a.containing_assembly, a.quantity_contained,

   7: CAST(l.quantity*l.cost AS DECIMAL(6,2))

   8: FROM list_of_parts l,airplane a

   9: WHERE l.assembly1 = a.contained_assembly)

  10: SELECT * FROM list_of_parts;

In the above query WITH clause defines the CTE called lsit_of_parts which contains three columns.The first select statement in the query will be invoked only once and accumulate the results of recursion process.

Restrictions for a CTE in a recursive query 

• The CTE definition must contain at least two select statements combined by UNION ALL operator.
• The number of columns in the anchor and recursive members must be the same.
• The data-type of a column in the recursive member must be the same as the data-type in the anchor member.
• The From clause of the recursive member refers only once to the name of the CTE.

The reference for the post is Beginners Guide to SQL server 2008 book.

The more about CTEs can read here