Writing readable SQL.
WHAT A COMMON TABLE EXPRESSION IS
A named subquery defined before the main query.
WHAT IT PROVIDES
Readability, by naming intermediate steps Reuse within one query Recursive queries
WHY READABILITY MATTERS SO MUCH HERE
Analytical queries become long, and deeply nested subqueries are unreadable.
HOW TO STRUCTURE A COMPLEX QUERY
A chain of named steps, each doing one thing.
WHAT THAT ACHIEVES
Each step can be tested independently by selecting from it.
WHAT TO BE CAREFUL WITH
Performance, since some databases materialise these and some inline them Very long chains, which become their own problem
WHAT RECURSIVE EXPRESSIONS SOLVE
Hierarchies: organisational structures, category trees, graph traversal.
WHAT THEY REQUIRE
An anchor case and a recursive case, combined.
WHAT TO ALWAYS ADD
A depth limit.
WHY
Cyclic data otherwise produces an infinite loop.
WHAT TO NAME STEPS
What they contain, not their position.
WHAT TO AVOID
Names like first, second and temp.
WHAT TO COMMENT
Why an unusual step exists.
