SQLite Forum

short circuiting subqueries with `IN`?
Yes.  Prior to 3.35.0 the default was NOT MATERIALIZED and a table in a CTE was materialized only under certain conditions in which the CTE itself contained a condition which acted as a flattening boundary.  The MATERIALIZED / NOT MATERIALIZED hint was added so that one could specifically control that boundary (or rather, specifically prohibit flattening by specifying MATERIALIZED, specifying NOT MATERIALIZED is the equivalent of specifying neither).

So for earlier versions it should work just fine as long as you add the terminating condition to the outer query since it is the query flattener which is triggering the short-circuit evaluation as part of flattening the subquery.