Menu
Nested CTEsCommon Table Expressions can be also nested. This means having multiple CTEs in the same query where at least one CTE refers to another CTE.
Cannot use with CTE"SELECT DISTINCT", GROUP BY, PIVOT, HAVING, Scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN, and Subqueries are not allowed in the CTE query definition of a recursive member. A CTE can be self-referencing and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.
CTEs can be recursive: A CTE can be used recursively, which a subquery cannot.
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.
While not strictly nested, you can use common table expressions to reuse previous queries in subsequent ones.
To do this, the form of the statement you are looking for would be
WITH x AS
(
SELECT * FROM MyTable
),
y AS
(
SELECT * FROM x
)
SELECT * FROM y
You can do the following, which is referred to as a recursive query:
WITH y
AS
(
SELECT x, y, z
FROM MyTable
WHERE [base_condition]
UNION ALL
SELECT x, y, z
FROM MyTable M
INNER JOIN y ON M.[some_other_condition] = y.[some_other_condition]
)
SELECT *
FROM y
You may not need this functionality. I've done the following just to organize my queries better:
WITH y
AS
(
SELECT *
FROM MyTable
WHERE [base_condition]
),
x
AS
(
SELECT *
FROM y
WHERE [something_else]
)
SELECT *
FROM x
With does not work embedded, but it does work consecutive
;WITH A AS(
...
),
B AS(
...
)
SELECT *
FROM A
UNION ALL
SELECT *
FROM B
EDIT Fixed the syntax...
Also, have a look at the following example
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