WITH (CTEs) make queries so much more readable and digestible. As a programmer who now does data and SQL, I latched on to these as soon as I found I could reduce repetition in a query with them.

Make sure you understand what optimization fences are and how they affect your performance. CTEs are nice to read but routinely destroy the performance.

[1] https://thoughtbot.com/blog/advanced-postgres-performance-ti...

As of Postgres 12 this has changed substantially. Instead of being materialized, CTEs are inlined and optimized with the rest of the query.

Exceptions:

1. If the results of the CTE are used more than once then it is materialized by default, though you can override this by adding "NOT MATERIALIZED" to the call.

2. Recursive and INSERT/UPDATE/DELETE CTEs are always materialized.

https://paquier.xyz/postgresql-2/postgres-12-with-materializ...

>CTEs are nice to read but routinely destroy the performance.

This may be true on archaic versions of MySQL and Postgres but is not the case today, barring some esoteric edge cases (bugs) where the optimizer gets thrown out of whack. Once while doing data science consulting I rewrote a ~1000 line query in Aurora (MySQL flavored) which had a ~2.5s runtime, which was far too slow for the client's use-case.

After rewriting all the CTEs (there were many) into subqueries, there was a 2-3% increase in query speed, barely (on the order of under a tenth of a second). There was a very tiny improvement far smaller than the normal variance of the runtime.

Then I rebuilt the query and the joins, and was able to get the query to consistently run in the range of 0.8 - 1.2s. For my own purposes I then duplicated the query, re-implemented the CTEs, and did validate that indeed there is only a negligible increase in query time when using CTE.

For monster queries (thousands of lines of SQL) I find that temporary tables are also great. You can index them and, depending on your DB and its settings, they're usually held in RAM so they're super fast.

That is implementation specific. In some db's putting an index on a temp table does nothing. In later versions of the same db it does but only in particular cases. Make sure you read the docs around that.

CTEs do not get cached though, so they are actually quite bad for repetition without also using a temp table.

Depends on DBMS and version. Some are smart enough to pipeline/tie or materialize it internally.

Ecto.Query in the Elixir universe seems like it was inspired by linq

WITH's are great and definitely solves some of the issues with SQL. Some implementations unfortunately don't optimize them as subqueries, but that is not the fault of SQL.