4. Understanding the Elements of SQL Statements : Common Table Expressions (CTEs) : Components of a CTE
 
Share this page                  
Components of a CTE
This simple example shows the components of a common table expression.
CREATE TABLE s (c INT, i INT);
INSERT INTO s VALUES (10,1),(20,2),(30,3),(100,15),(200,15),(300,15);
COMMIT;
/* Define the CTE expression and column list */
WITH t(z) AS
/* Define the CTE query */
(
        SELECT c FROM s WHERE i > 10
 )
/*Define outer query that references the CTE name */
SELECT t.z FROM t ORDER BY 1;
Executing . . .
z           
-------------
          100
          200
          300
(3 rows)
The t is the name of the first (and in this case only) WITH element (also known as a CTE). There can be multiple WITH elements in the same query. Each WITH element can be referred to by its name as though it were a view. The scope of each WITH element extends to the right through the rest of the SQL statement. WITH elements can refer to others that have been declared already in the current statement. For example:
WITH
 t(z) AS (
        SELECT c FROM s WHERE i > 10
 ),
 u(a,i) AS (
        SELECT z, 15 FROM t
 )
SELECT u.a,s.i FROM u,s WHERE s.i=u.i ORDER BY 1;
Executing . . .
a             i
------------- ------------
          100           15
          100           15
          100           15
          200           15
          200           15
          200           15
          300           15
          300           15
          300           15
(9 rows)