WITH is the let of SQL. It’s incredibly useful for comprehension, even if it’s a little bit slower.

It also comes in handy when you are generating SQL. I’ve come across this situation a few times: you have to generate a statement/query that has an IN check against a very large set of identifiers. I’ve done some experiements and by changing them into joins against a CTE, they actually got faster. So from this

SELECT whatever FROM t WHERE id IN (...)

to this

WITH v(id) AS (VALUES ...) SELECT whatever FROM t JOIN v ON (t.id = v.id)

While I try to avoid the “very large set of identifiers” problem, it crops up in some legacy things I have to support and this is proven to be quite useful.