Multiple CTE in single query

Question

Is it possible to combine multiple CTEs in single query with arel? I am looking for way to get result like this:

WITH cte1 AS (
...
),
WITH RECURSIVE cte2 AS (
...
),
WITH cte3 AS (
...
)
SELECT ... FROM cte3 WHERE ...

As you can see, I have one recursive CTE and two non recursive.

Solution 1

Use the key word WITH once at the top. If any of your Common Table Expressions (CTE) are recursive (rCTE) you have to add the keyword RECURSIVE at the top once also, even if not all CTEs are recursive:

WITH RECURSIVE
  cte1 AS (...)         -- can still be non-recursive
, cte2 AS (SELECT ...
           UNION ALL
           SELECT ...)  -- recursive term
, cte3 AS (...)
SELECT ... FROM cte3 WHERE ...

The manual:

If RECURSIVE is specified, it allows a SELECT subquery to reference itself by name.

Bold emphasis mine. And, even more insightful:

Another effect of RECURSIVE is that WITH queries need not be ordered: a query can reference another one that is later in the list. (However, circular references, or mutual recursion, are not implemented.) Without RECURSIVE, WITH queries can only reference sibling WITH queries that are earlier in the WITH list.

Bold emphasis mine again. Meaning that the order of WITH clauses is meaningless when the RECURSIVE key word has been used.

BTW, since cte1 and cte2 in the example are not referenced in the outer SELECT and are plain SELECT commands themselves (no collateral effects), they are never executed (unless referenced in cte3).

Solution 2

Yes. You don't repeat the WITH. You just use a comma:

WITH cte1 AS (
...
),
     cte2 AS (
...
),
     cte3 AS (
...
)
SELECT ... FROM 'cte3' WHERE ...

And: Only use single quotes for string and date constants. Don't use them for column aliases. They are not allowed for CTE names anyway.

Solution 3

As the accepted answer correctly says, the with clause is used only once per a CTE chain. However, for sake of completeness, I would like to add it does not stop you from nesting CTEs.

If cte2 uses cte1, cte3 uses cte2 etc., then the dependency chain between CTEs is linear and it is expressed as with with 3 CTEs. On the contrary, if cte2 doesn't need cte1 and both are needed only in cte3 it should be considered to nest them under definition of cte3 (with cte3 as (with cte1 as (...), cte2 as (...) select...)).

The syntax of CTEs then reflects the dependency tree between CTEs and literally visualizes the scope of partial datasets which can improve readability and prevents scope leakage bugs. Not all db vendors support it but Postgres does.

Example:

with cte1(id,capital) as (
  values(1,'Prague'),(2,'Bratislava')
), cte2(id,code) as (
  with cte2inner1(id,code) as (
    values(1,'CZ'),(2,'SK')
  ), cte2inner2(id,country) as (
    values(1,'Czech Republic'),(2,'Slovakia')
  )
  select id,country from cte2inner1 join cte2inner2 using (id)
) 
select *
from cte1 join cte2 using (id)
--join cte2inner1  not possible here

Comments

[axvm]

But if I need one recursive CTE with 2 non recursive?

[thadk]

But cte1, cte2, cte3 cannot refer to each-other, correct?

[Gordon Linoff]

@thadk . . . They can, there is no limitation on that, other than the CTEs need to be defined before they are used.

[thadk]

Thanks! I couldn't get it to work when I tried to refer to cte1 within cte2, but I may have made a typo or something.

[L.Newell]

Erwin Trendsetter laying down that CTE knowledge. +1 - thanks for the insight!