Mega Code Archive

 

Categories / MSSQL Tutorial / Transact SQL

 

Common Table Expressions

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)