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.
I was morbidly curious what a "good CTE" could possibly be...
Seems to be this:
> Chronic traumatic encephalopathy (CTE) is a progressive neurodegenerative disease […]
> Evidence indicates that repetitive concussive and subconcussive blows to the head cause CTE. In particular, it is associated with contact sports such as boxing, American football, Australian rules football, wrestling, mixed martial arts, ice hockey, rugby, and association football.
https://en.wikipedia.org/wiki/Chronic_traumatic_encephalopat...
The NFL in the US has famously gone to great lengths to downplay the impact of CTE on current and retired players. And there have been several famous players who literally lost their minds as they aged, and we now know that was due to CTE. Something like 90% of ex-NFLers have it. The number is still really bad for collegiate players. And even high school players are at risk.
It was to the point that Will Smith starred in a movie about it. https://en.wikipedia.org/wiki/Concussion_(2015_film)
I assumed the C stood for Concussion. Wrong but also partly right!
edit: syntax. voice to text was liberal with the comma abuse
It'd be quite surprising the WITH statement in top a query to be the first feature to learn/use past basic SQL. Is it personal experience in some industry?
Sure, yes, OP should (and now has) defined the term. But at the same time it's reasonable to expect that someone reading a blog post on BoringSQL.com would already know the term just as much as we could expect people interested in Clojure would know what a REPL is.
This is a valuable comment, don't ruin it with sarcasm and rudeness.
no it wouldn't
the whole point is to critique the post
Regarding recursive CTEs, you might be interested in how DuckDb evolved them with USING KEY: https://duckdb.org/2025/05/23/using-key
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.
All that is supported with CTEs as well. And both Postgres and Oracle support the SQL standard for these things.
You can't choose between breadth first/depth first using CONNECT BY in Oracle. Oracle's manual even states that CTE are more powerful than CONNECT BY
Obviously makes only sense for stuff like analytical queries that are not running constantly.
I just rewrote all queries with claude code and next day and compute decreased to 1/5th.
I'll write some nice clean CTEs and then have to refactor it to temp tables due to the lack of materialization.
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).