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
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.
When we execute above query, we will get the following result
Msg 207, Level 16, State 1, Line 4 Invalid column name 'Hire_Month'.
The result of a table expression is always a table or expression.
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
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.
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.
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.
WITH cte_name(column_list) AS (inner_query)
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.
WITH cte_name (column_list) AS
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.
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