sql-patterns

Recursive CTEs use WITH clauses to anchor a base query, then iteratively join to themselves. Start with your root rows (e.g., top-level employees), then UNION ALL to join each level to its children, specifying a termination condition. This pattern works across PostgreSQL and other SQL systems for org charts, bill-of-materials, and reporting hierarchies.

Index on columns in WHERE clauses and JOIN conditions first. Analyze join order—filter early and join smallest result sets last. Use EXPLAIN to see execution plans. Index strategies vary by join type: INNER JOINs benefit most from indexes on foreign keys, while LEFT JOINs require indexes on the joined table's key columns to avoid full table scans.

Window functions like SUM() OVER (ORDER BY column) compute aggregates across row ranges without collapsing groups. Use ROWS BETWEEN to define the window frame—UNBOUNDED PRECEDING for running totals, or N PRECEDING for moving averages. Partition by relevant columns to reset calculations per group.

CTEs improve readability for complex queries and enable recursion, which subqueries cannot. Use CTEs when multiple subqueries repeat logic or when recursion is needed. Subqueries are lighter-weight for single-use cases; temporary tables persist across statements but require cleanup, making CTEs the middle ground for multi-step logic.

Yes. Define a CTE to prepare intermediate results, then apply window functions in the main SELECT. This pattern is common for hierarchical aggregations—build the hierarchy with a recursive CTE, then rank or sum rows using window functions to produce final analytics.

Avoid Cartesian products from unspecified join conditions. Don't use SELECT * with joins unless you need all columns—specify explicitly to reduce memory. Avoid correlated subqueries in loops; use joins instead. Don't over-index; each index slows writes. Test EXPLAIN output to catch inefficient query plans before deploying.