A Common Table Expression (CTE) creates a temporary query that can be referenced within the scope of a SELECT, INSERT, UPDATE, or DELETE query.
The basic syntax for a CTE is as follows:
WITH expression_name [ ( column_name [ ,...n ] ) ]
AS ( CTE_query_definition )
The arguments of a CTE are described in the following table.
Argument Description
expression_name The name of the common table expression.
column_name [ ,...n ] The unique column names of the expression.
CTE_query_definition The SELECT query that defines the common table expression.
A non-recursive CTE is one that is used within a query without referencing itself.
It serves as a temporary result set for the query.
A recursive CTE is defined similarly to a non-recursive CTE, only a recursive CTE returns hierarchical self-relating data.
Using a CTE to represent recursive data can minimize the amount of code needed compared to other methods.
Referenced from:
SQL Server 2005 T-SQL Recipes A Problem-Solution Approach
5> create table freights
6> (orderid INT NOT NULL,
7> orderdate DATETIME,
8> shippeddate DATETIME,
9> freight MONEY,
10> price MONEY)
11> GO
1> INSERT INTO freights VALUES (1111, '1.10.2005','1.20.2005', 30.45, 200.25)
2> INSERT INTO freights VALUES (2222, '2.11.2005', '2.21.2005', 89.25, 543.00)
3> INSERT INTO freights VALUES (3333, '3.12.2005', '3.22.2005', 19.35, 120.25)
4> INSERT INTO freights VALUES (4444, '4.13.2005', '4.23.2005', 9.99, 154.35)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select orderid from freights
2> where price > (SELECT AVG(price) from freights WHERE YEAR(orderdate) = '2005')
3> AND freight > (SELECT AVG(price)
4> from freights
5> WHERE YEAR(orderdate) = '2005')/10
6> GO
orderid
-----------
2222
(1 rows affected)
1> --A better way is to write a common table expression using the WITH clause.
2>
3> WITH price_calc (year_2005) AS
4> (SELECT AVG(price)
5> from freights
6> WHERE YEAR(orderdate)='2005')
7> SELECT orderid
8> FROM freights
9> WHERE price > (SELECT year_2005
10> FROM price_calc)
11> AND freight > (SELECT year_2005
12> FROM price_calc)/10;
13> drop table freights;
14> GO
orderid
-----------
2222
(1 rows affected)