SQL - use CTE reference to another CTE
Is it possible that SQL is using a reference inside a Common Table expression inside another CTE in the same query? Here's 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 is part of standard SQL. Any advice regarding this argument would be much appreciated. Thank you very much!
source to share
Three important properties of CTEs are:
-
You can refer to CTEs in subsequent CTEs or in the main body of the request.
-
You can reference any given CTE multiple times.
-
CTE can be used in a clause
from
at any nesting level in other subqueries.
CTEs - like everything in SQL - must be defined prior to using them. Thus, you cannot randomly identify them.
This is the standard definition of CTEs and explains well how they are used in databases. These three properties are key in ways that differ from subqueries.
source to share