Using EXCEPT in a recursive common table expression

33 votes

2 answers

12192 views

sql-server sql-server-2008 execution-plan recursive except

                          Why does the following query return infinite rows?  I would have expected the EXCEPT clause to terminate the recursion..

	with cte as (
		select *
		from (
			values(1),(2),(3),(4),(5)
		) v (a)
	)
	,r as (
		select a
		from cte
		where a in (1,2,3)
		union all
		select a
		from (
			select a
			from cte
			except
			select a
			from r
		) x
	)
	select a
	from r

I came across this while trying to answer a question  on Stack Overflow.
                        

Asked by Tom Hunter (2179 rep)

Dec 22, 2011, 01:27 AM
Last activity: Apr 27, 2021, 06:17 AM

Using EXCEPT in a recursive common table expression

Related Questions