Recursive CTE With UNION gives error
why can’t we use , just “UNION” instead of “UNION ALL” in the recursive CTE’s. If I use just UNION in the recursive CTE it is throwing error. Can you please help me out?
Example,
CREATE TABLE te (sno int)
Go
INSERT INTO te VALUES (1),(4),(10)
Go
;WITH ctemax(maxsno,minsno) AS (SELECT max(sno),MIN(sno) FROM te),
cte (sno)AS(
SELECT minsno FROM ctemax
UNION
SELECT cte.sno+1 FROM cte
INNER JOIN (SELECT maxsno FROM ctemax)tbl
ON cte.sno<tbl.maxsno
)
SELECT cte.sno from cte LEFT OUTER JOIN te
ON cte.sno=te.sno
WHERE te.sno IS null
Result
Msg 252, Level 16, State 1, Line 1
Recursive common table expression 'cte' does not contain a top-level UNION ALL operator.
Note : Will work fine with UNION ALL
Software/Hardware used:
SQL Server 2008 R2
ASKED:
July 23, 2012 12:37 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
That is like asking why you can’t say “PICK fields from file where…”
Because the syntax says you have to.
why can’t we use , just “UNION”instead of “UNION ALL” in the recursive CTE’s.
Because if you use just UNION in the CTE, it throws an error.
BigKat’s comment sums that up nicely.
In perhaps more detail, it’s partly because of how different UNION and UNION ALL are. UNION by itself is actually UNION DISTINCT which will give a different result than UNION ALL will give. Try working a CTE example manually to learn how the internal logic would be complicated by UNION DISTINCT.
Tom