Multiple CTE in single query
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.
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 aSELECT
subquery to reference itself by name.
Bold emphasis mine. And, even more insightful:
Another effect of
RECURSIVE
is thatWITH
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.) WithoutRECURSIVE
,WITH
queries can only reference siblingWITH
queries that are earlier in theWITH
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
).
Problem Reason: Here, you don't have to use multiple WITH clause for combine Multiple CTE.
Solution: It is possible to create the Multiple Common Table Expression's using single WITH clause in SQL. The two different CTE's are created using Single WITH Clause and this is separated by comma to create multiple CTE's.
Sample Multiple CTE's using single
With EmpCount1(DeptName,TotalEmployees)
as
(
Select DeptName, COUNT(*) as TotalEmployees
from Tbl_EmpDetails
join Tbl_Dept Dept
on Tbl_EmpDetails.DeptId = Dept.DeptId
WHERE DeptName IN ('BI','DOTNET')
group by DeptName
),
EmpCount2(DeptName,TotalEmployees)
as
(
Select DeptName, COUNT(*) as TotalEmployees
from Tbl_EmpDetails
join Tbl_Dept Dept
on Tbl_EmpDetails.DeptId = Dept.DeptId
WHERE DeptName IN ('JAVA','AI')
group by DeptName
)
Select * from EmpCount1
UNION
Select * from EmpCount2
This is sample syntax for creating multiple Common Table Expression's with a single With Clause.
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