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.

Answer 1

The short answer would be: no.

However, you could use a view, that had the with statement in it, then use a with on the view... but that gets a little complicated.

Answer 2

Yes you can. This would be the form of the statement you are looking for:

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

Answer 3

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

Answer 4

With does not work embedded, but it does work consecutive

WITH A AS( ... ), WITH B AS( ... )