Advanced SQL Demystified
Advanced SQL is not a different language from the SQL you already write, it is the same declarative model stretched to cover problems that used to force a trip back into application code.
CTEs, window functions, LATERAL joins, and recursive queries all exist to let one statement describe logic that would otherwise need loops, temp tables, or several round trips to the database.
This page builds the mental model behind those four tools so that the dedicated pages for CTEs, window functions, LATERAL joins, and recursive CTEs read as applications of one idea rather than four unrelated syntaxes to memorize.
Summary
- Core Idea: Advanced SQL features let you name intermediate results, compute across row sets without collapsing them, correlate per-row subqueries, and iterate to a fixed point, all inside one declarative statement.
- Why It Matters: Expressing this logic in SQL keeps the work close to the data, avoids row-by-row round trips, and gives the query planner full visibility to optimize the whole computation together.
- Key Concepts: common table expression (CTE), window function, partition, LATERAL correlation, recursive fixed point.
- When to Use: Building reports that rank or compare rows within groups, walking hierarchies or graphs, pulling a "top N per group" result, or naming a multi-step transformation for readability.
- Limitations / Trade-offs: These tools trade some raw simplicity for expressive power, and a badly shaped CTE or recursive query can hide a performance problem that a plain join would have exposed immediately.
- Related Topics: query planning and EXPLAIN, index-supported sorting, materialized views, set-based thinking versus procedural loops.
Foundations
Every advanced SQL feature on this page is still just a query over sets of rows, the difference is only in how much structure you can impose on that computation before the final result comes back.
A CTE (the WITH clause) is nothing more than a named subquery that you can reference later in the same statement, which exists purely to make a long query readable by breaking it into named steps.
A window function computes a value across a set of related rows, called its partition, without merging those rows into one output row the way GROUP BY would.
LATERAL lets a subquery in the FROM clause see columns from FROM items that appear before it, effectively running a small correlated query once per outer row while still producing a single set-based result.
A recursive CTE repeats a query against its own growing result until no new rows appear, which is how SQL expresses hierarchy walks and graph traversal without a client-side loop.
The simplest way to hold all four in your head is to think of a SQL statement as an assembly line: CTEs name the stations, window functions add computed columns without breaking the line into separate belts, LATERAL lets one station look back at a previous station's output row by row, and recursion lets a station feed its own output back in until the line naturally stops.
Mechanics & Interactions
The query planner processes a statement's clauses in a fixed logical order that is different from the order you type them, and understanding that order explains why window functions can reference GROUP BY results but not the other way around.
FROM / JOIN -> WHERE -> GROUP BY -> HAVING
-> window functions -> SELECT list
-> DISTINCT -> ORDER BY -> LIMIT / OFFSETBecause window functions run after grouping and filtering but before the final SELECT list is assembled, they can rank or compare already-aggregated rows without needing a second pass over the table.
A non-recursive CTE is not automatically an "optimization fence" the way it was in older PostgreSQL versions, since PostgreSQL 12 the planner can inline a WITH query into the surrounding statement exactly like a subquery unless it is referenced more than once or marked MATERIALIZED.
That distinction matters because an inlined CTE lets the planner push filters down into it, while a materialized one is computed once and then read back, which can help when the same CTE is reused several times but can hurt when it prevents filter pushdown.
LATERAL correlation is mechanically similar to a per-row subquery, but because it lives in the FROM clause it can return multiple columns and multiple rows per outer row, which is exactly what a plain correlated subquery in the SELECT list cannot do.
SELECT a.id, recent.total
FROM app.accounts a
JOIN LATERAL (
SELECT total FROM app.orders o
WHERE o.account_id = a.id
ORDER BY o.created_at DESC LIMIT 1
) recent ON true;Recursive CTEs execute as a working table loop: the non-recursive term seeds the first batch of rows, the recursive term runs again against only the rows produced in the previous iteration, and the loop stops the instant an iteration produces zero new rows.
That loop has no built-in cycle detection, so a self-referencing hierarchy with a cycle will spin until you add an explicit guard, usually an array of visited keys checked with NOT (id = ANY(path)).
Advanced Considerations & Applications
Window functions have no dedicated index type of their own, but an index that matches the PARTITION BY and ORDER BY columns lets the planner avoid an explicit sort step before computing the window, which is often the single biggest cost in a window query.
Recursive CTEs pay a real materialization cost per iteration because PostgreSQL has to store the working table between steps, so a graph walk over a large, poorly bounded table can consume far more work_mem and time than the equivalent bounded loop in application code.
Pivoting rows into columns can be done with conditional aggregation using FILTER, with the tablefunc extension's crosstab(), or with jsonb_object_agg() when the target columns are not known ahead of time, and each of those trades fixed-shape simplicity for dynamic flexibility differently.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
CTE (WITH) | Names intermediate steps for readability and reuse | Materialization can block filter pushdown if reused or forced | Breaking a long query into reviewable, named stages |
| Window function | Computes per-row ranks or running values without collapsing rows | No dedicated index type, relies on sort-friendly indexes | Top-N per group, running totals, row-to-row comparisons |
| LATERAL join | Correlated subquery that can return multiple rows and columns per outer row | Needs a supporting index on the correlated column or it degrades to nested loops over everything | Per-row "latest N" lookups a plain join can't express |
| Recursive CTE | Expresses hierarchy and graph traversal declaratively | Materializes a working table per iteration, needs explicit cycle guards | Org charts, bill-of-materials, dependency graphs |
FILTER pivot | No extension required, planner-friendly single scan | Column set must be known at query time | Small, fixed sets of pivot columns |
Many teams reach for application-side loops out of habit even after a query could express the same logic declaratively, and the honest trade-off is that SQL versions of these patterns are less familiar to read at a glance but avoid the latency of shipping rows back and forth between the database and the application tier.
Common Misconceptions
- "Every CTE is an optimization fence." That was true before PostgreSQL 12, but a plain
WITHquery today is inlined like a subquery unless it is referenced multiple times or explicitly markedMATERIALIZED. - "Window functions need
GROUP BYto work." A window function can run over the entire result set with noGROUP BYat all, sincePARTITION BYinside theOVERclause defines its own grouping independent of the query'sGROUP BY. - "LATERAL is just a correlated subquery." A correlated subquery in the
SELECTlist can only return one scalar value per outer row, whileLATERALin theFROMclause can return multiple rows and columns per outer row. - "Recursive CTEs use real recursion like a function call stack." PostgreSQL evaluates them iteratively as a fixed-point loop over a working table, not as nested function calls, which is why runaway recursion shows up as an ever-growing working table rather than a stack overflow.
- "Pivoting always requires the
tablefuncextension." Conditional aggregation withFILTERhandles fixed, known pivot columns without installing anything extra.
FAQs
What is the actual difference between a CTE and a subquery?
A CTE is a named subquery declared in a WITH clause, and since PostgreSQL 12 the planner treats a singly-referenced, non-recursive CTE exactly like an inline subquery unless you force materialization.
When should I force a CTE to materialize?
- When the same CTE is referenced multiple times and recomputing it would be wasteful
- When you want a deliberate optimization boundary to stabilize a plan
- When the CTE performs a side-effecting
INSERT/UPDATE/DELETEthat must run exactly once
Do window functions run before or after `GROUP BY`?
Window functions run after GROUP BY and HAVING but before the final SELECT list is assembled, so they can operate on already-grouped rows.
Can I filter directly on a window function's result?
Not in the same SELECT, since window functions are computed after WHERE, so you need to wrap the query in a CTE or subquery and filter the outer query instead.
Why does my window function query feel slow even on a small table?
It is usually the sort behind PARTITION BY/ORDER BY rather than the window computation itself, and an index matching those columns often removes that sort entirely.
How is LATERAL different from a normal JOIN?
A normal JOIN condition cannot reference columns computed inside the joined subquery, while LATERAL lets the subquery on the right see columns from FROM items on its left, evaluated once per outer row.
What's a realistic use case for LATERAL?
Pulling each account's single most recent order, or its top three orders, in one query instead of running a separate query per account from application code.
Why does my recursive CTE never stop?
- The recursive term is producing new rows every iteration, often because the source data actually contains a cycle
- There is no explicit guard tracking visited rows
- Fix it by carrying a visited-path array and checking
NOT (id = ANY(path))before recursing further
Is a recursive CTE the same as recursion in a programming language?
No, it is an iterative fixed-point loop over a working table, evaluated by the executor step by step rather than through nested function calls.
Do I need the `tablefunc` extension to pivot data?
Only when the pivot columns are not known ahead of time or you want crosstab()'s convenience, since conditional aggregation with FILTER handles fixed column sets without any extension.
Can CTEs and window functions be combined?
Yes, and it is a common pattern to compute a window function inside a CTE and then filter or join against its result in the outer query, since window functions cannot be filtered in the same SELECT they appear in.
What's the single biggest performance mistake with these features?
Assuming that naming something in a WITH clause automatically makes it faster, when in reality the planner's inlining behavior means a CTE's performance is usually identical to writing the same logic as a plain subquery.
Should I always prefer these SQL features over application-side loops?
Prefer them when the logic is naturally set-based and would otherwise cost multiple round trips, but keep genuinely procedural, branching business logic in application code where it is easier to test and reason about.
Related
- Advanced SQL Basics - hands-on examples of CTEs, window functions, and LATERAL
- Common Table Expressions - CTE syntax, naming, and materialization in depth
- Window Functions - the full
OVER,PARTITION BY, and frame syntax - Recursive CTEs - hierarchy and graph walks with cycle guards
- LATERAL Joins - correlated per-row subqueries in the
FROMclause - EXPLAIN Basics - reading the plan behind any of these queries
Stack versions: This page was written for PostgreSQL 18.4 (stable line 18, maintenance line 17), where CTE inlining behavior introduced in PostgreSQL 12 and the window function and recursive query mechanics described here remain current.