Wann würdest du eine CTE statt einer Subquery benutzen?

Nimm eine CTE, wenn Lesbarkeit zählt oder wenn dasselbe Zwischenergebnis mehr als einmal referenziert wird, denn sie benennt einen Schritt, statt ihn zu verschachteln. Für einen einzelnen kleinen Inline-Filter ist eine Subquery völlig in Ordnung. Die Performance ist auf den meisten modernen Engines ungefähr gleich, manche materialisieren CTEs und manche inlinen sie, eine CTE ist also nicht automatisch schneller oder langsamer als die entsprechende verschachtelte Query.

Vor allem wegen Lesbarkeit und Wiederverwendung. Wenn eine Query vier logische Schritte hat, lesen sich vier benannte CTEs wie ein Absatz und ein Reviewer kann der Argumentation folgen, während drei Ebenen verschachtelter Subqueries bedeuten, dass man in der Mitte anfängt und sich nach außen arbeitet. Der andere echte Grund ist, dieselbe Zwischenmenge zweimal zu referenzieren, ohne sie zu wiederholen. Was ich nicht behaupten würde: dass CTEs schneller sind. Dieser Mythos ist überall. Je nach Engine und Version wird eine CTE in den Plan geinlined oder einmal materialisiert, und beides kann das Schnellere sein, abhängig davon, wie oft sie referenziert wird und wie selektiv sie ist. Auf Postgres vor Version 12 waren CTEs ein Optimization Fence, was sie gelegentlich dramatisch langsamer gemacht hat, deshalb schaue ich in den Plan, wenn Performance wirklich zählt. Wo eine CTE einzigartig nützlich ist, ist Rekursion: eine Vorgesetztenhierarchie oder einen Kategoriebaum durchzulaufen ist mit einer rekursiven CTE geradeaus und ohne sie ehrlich unangenehm.