common table expression - SQL - Use a reference of a CTE to another CTE

Question

Is it possible in SQL use a reference inside a Common Table Expression inside another C.T.E in the same query? Here there is an example:

WITH CT1 AS (SELECT * FROM T),
     CT2 AS (SELECT * FROM CT1)

SELECT * FROM CT2;

I tried this in SQLite3 and it works, I just wanted to know if it's part of standard SQL. Any advices concerning this argument will be highly appreciated. Thank you very much!

Answer 1

Here are three important properties of CTEs:

• You can refer to a CTE in subsequent CTEs or in the main body of the query.

• You can refer to any given CTE multiple times.

• The CTE can be used in a from clause at any level of nesting within other subqueries.

The CTEs -- as with everything in SQL -- need to be defined before they are used. So, you cannot define them in random order.

This is the standard definition of CTEs and does a good job of explaining how they are used across databases. Those three properties are key ways that they differ from subqueries.