SQLite Forum

Avoiding recursive CTE materialisation
Login
That will not work because, while it will stop searching the branch of the tree beyond the desired termination condition, other branches not containing the desired termination condition (found_what_I_want) will be searched to exhaustion.  Only after a search of every branch has either found the terminating condition or been exhausted will the processing end.  Basically this does something less than a full exhaustive search, but not by much.

If the CTE were not materialized then the recursive query would run returning one row by each for inspection for the terminating condition which, if found, would terminate the entire statement processing (all branches, not just the one containing the desired condition).