Can you create nested WITH clauses for Common Table Expressions?

Question

WITH y AS (
    WITH x AS (
        SELECT * FROM MyTable
    )
    SELECT * FROM x
)
SELECT * FROM y

Does something like this work? I tried it earlier but I couldn't get it to work.

Solution 1

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

Solution 2

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

Solution 3

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

SQLFiddle DEMO

Comments

[Jason TEPOORTEN]

Thank you very much. I was able to do it in Oracle: WITH J AS (SELECT 1 AS ONE FROM DUAL), Q AS (SELECT J.*, 2 AS TWO FROM J) SELECT * FROM Q

[Joe Phillips]

My original question never said anything about unioning data together. It could have just as easily been joining data

[Joe Phillips]

You're a little late to the party ;)

[symbiont]

this is not nested

[Reversed Engineer]

Yes, this is not nested, just a second CTE referencing a first CTE

[peterh]

Essentially the post means, that you can't do it, but it is not a big problem.

[Joe Phillips]

Yes, this is an acceptable answer because what I was trying to achieve with nesting is the same thing this ends up giving me anyway

[Meower68]

and that's consecutive CTEs, not nested CTEs

[Christiaan Westerbeek]

Stating that this is not nested, just because query 2 is not inside the parenthesis of query 1, sounds like a weak argument. I think it's nested (not recursively nested), because query 2 uses the result of query 1, which occurs with nesting too. Is defined that nesting can only be when a child is within its parent parenthesis (or similar) symbols?

[Stonetip]

It’s more akin to chaining, but whatever. It gets the job done and solved a similar issue for me in a simple, readable way.

[Teamothy]

Awesome suggestion with putting main SELECT into next WITH clause and then SELECT from last WITH query. Thank You :)