Abstract

We derive new iterative CTE variants from the simple loop-based, operational semantics of SQL:1999’s WITH RECURSIVE. In the absence of fixpoint-based semantics and monotonicity restrictions, these CTE variants (1) can hold onto as well as forget the results of earlier loop iterations or (2) maintain iteration results in a keyed table, enabling a SQL authoring style that mimics imperative algorithms. We exercise the new variants using a series of examples to demonstrate that this fresh look at CTEs has a beneficial impact on the readability and performance of iterative SQL queries.