CTE stands for Common Table Expressions in SQL. They are temporary result sets defined within a single query using the WITH clause, acting like named subqueries to improve readability and structure.

If you want something that is more like actual recursion (I.e., depth-first), Oracle has CONNECT BY which does not require the same kind of tracking. It also comes with extra features to help with cycle detection, stack depth reflection, etc.

If your problem is aligned with the DFS model, the oracle technique can run circles around recursive CTEs. Anything with a deep hierarchy and early termination conditions is a compelling candidate.

Regarding recursive CTEs, you might be interested in how DuckDb evolved them with USING KEY: https://duckdb.org/2025/05/23/using-key

Embarassingly, despite thinking of myself as pretty knowledgeable with SQL, I had no idea you could nest DML statements inside a CTE. I always assumed/used DML statements as the final statement after a CTE was defined. I'm not sure if or when I might use this in the future, but it's neat to learn something new (and to be humbled at the same time).

Obviously makes only sense for stuff like analytical queries that are not running constantly.

I'll write some nice clean CTEs and then have to refactor it to temp tables due to the lack of materialization.

I just rewrote all queries with claude code and next day and compute decreased to 1/5th.

These days I often write queries like this (especially when doing exploratory ad hoc queries, but also in apps) even when it's not necessary to use a CTE:

    WITH
      a AS (
        SELECT ... FROM ... WHERE ... etc.
      )
    SELECT * FROM a

This has a bunch of nice benefits. For example, it allows me to add steps incrementally, and to "comment out" a step I can simply change the next step's input to read from the preceding step. Each step can be read and understood in isolation.

I work a lot with Postgres, ClickHouse, and SQLite, and generally find that the database inlines and optimizes CTEs, and challenges mostly concern performance traps (like IN or EXISTS) that allly to non-CTE situations as well.