Learn SQL Database Programming

Using non-recursive CTEs

The syntax for a single non-recursive CTE is as follows:

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

The syntax for a non-recursive CTE with multiple CTEs is as follows:

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; 

The following diagram will help you understand what each piece of the CTE is: