Learn SQL Database Programming

Syntax for common table expressions

For a single non-recursive CTE, you can use the following sample syntax:

WITH ctename (col1, col2, colN) AS (SELECT col1, col2, colN FROM table)SELECT col1, col2, colN FROM ctename;

For a non-recursive CTE with multiple CTEs, you can use the following sample syntax:

WITH ctename1 (col1, col2, colN) AS (select col1, col2, colN from table1), ctename2 (col1, col2, colN) AS (select col1, col2, colN from table2) SELECT col1, col2, colN FROM ctename1JOIN ctename2 ON ctename1.col1 = ctename2.col1;

For a recursive CTE, you can use the following sample syntax:

WITH RECURSIVE ctename AS ( initial query  UNION ALL recursive query )SELECT * FROM ctename;