Subqueries, CTEs, Set Operations, and Window Functions
Subqueries and CTEs name intermediate relations; set operations combine compatible results; window functions calculate across related rows without collapsing them. These tools are valuable when each step has a declared grain. They become dangerous when layers hide duplicates, repeated work, unbounded recursion, or ordering assumptions that the final result never states.
What will you be able to do?
- Choose scalar, correlated, EXISTS, and derived-table subqueries.
- Structure readable queries with CTEs.
- Use UNION, INTERSECT, and EXCEPT correctly.
- Apply ranking, running aggregates, lag/lead, and recursive traversal.
Prerequisites and mental model
A CTE is a named query result inside one statement, not automatically a temporary table. A window function keeps each row and opens a “window” of peer rows for calculation. Set operations require compatible columns and distinguish duplicate-removing from duplicate-preserving forms.
How do windows preserve detail?
Rank tickets within each organization and compare creation time:
Unlike GROUP BY, every ticket remains.
How do CTEs make grain visible?
Each CTE name and columns expose the intermediate grain.
How do set operations behave?
- UNION removes duplicate result rows.
- UNION ALL preserves them and is usually cheaper.
- INTERSECT keeps rows present in both.
- EXCEPT keeps rows from the first absent in the second.
Use EXCEPT in data reconciliation: expected authorized document IDs minus retrieved authorized IDs reveals missing candidates.
Recursive CTEs can traverse reply trees or organizational hierarchies. Always define termination and consider a depth guard or cycle detection.
Why does this matter in AI-native systems?
Windows support evaluation ranking, latency sequences, and per-tenant sampling. Set difference compares expected and actual retrieval. Recursive queries can assemble conversation or document hierarchies—but unbounded recursion and uncontrolled context expansion create latency and token risks.
Prove it worked
Build a six-row fixture across two organizations. Calculate expected ranks by hand. Test ties with row_number, rank, and dense_rank. Use UNION ALL and UNION on deliberate duplicates and record the count difference. Verify every window has a deterministic ordering.
Production judgment
- CTEs can be inlined or materialized depending on query and directives; inspect the plan.
- Large sorts and windows may spill to disk.
- Default window frames can surprise functions over peer rows; specify frames when cumulative semantics matter.
- Recursive CTEs need tenant scope, cycle behavior, and bounds.
- Set duplicate removal requires work; choose it for semantics, not habit.
Common mistakes
- Running total jumps across ties: default frame groups peers → specify ROWS frame and tie-breaker.
- Top N is nondeterministic: incomplete order → add stable key.
- CTE query is unexpectedly slow: repeated/materialized work → inspect plan and restructure based on evidence.
- Recursion never finishes: missing cycle/depth control → track path and bound depth.
AI Pair-Programmer Prompt
Hands-on exercise
For each organization, return the two most recently active tickets based on their latest message, while retaining tickets with no messages last.
Acceptance criterion: ties resolve by ticket ID, each ticket appears once, tenant keys are preserved, and a hand-built fixture matches expected ordering.
Knowledge check
- How does a window differ from GROUP BY?
- What is the semantic difference between UNION and UNION ALL?
- Why specify a window frame?
- What two protections does recursion need?
Answers
- Windows calculate across rows while preserving them; grouping collapses rows per group.
- UNION removes duplicates; UNION ALL preserves them.
- To make cumulative/peer behavior explicit rather than relying on a surprising default.
- A termination rule plus cycle/depth and scope controls.
Completion checklist
Primary references