Menu
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!
676
Not only can you define multiple CTEs and reference them in a single SELECT statement, but you can also have a CTE that references another CTE. In order to do this all you need to do is define the referenced CTE prior to using it. Here is an example where my first CTE is referenced inside the second CTE definition.
After you define your WITH clause with the CTEs, you can then reference the CTEs as you would refer any other table. However, you can refer a CTE only within the execution scope of the statement that immediately follows the WITH clause.
A straightforward question deserves a straightforward answer: yes, you can. Now that you know how to use multiple CTEs, writing a CTE that references another CTE is just a variation of what you've learned.
A CTE is similar to a derived table in that it is not stored and lasts only for the duration of the query. Unlike a derived table, a CTE behaves more like an in-line view and can be referenced multiple times in the same query. Using a CTE makes complex queries easier to read and maintain.
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.
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
