The SQL Query
A SQL query looks like a simple sentence, but it is actually a declarative specification of a result set that PostgreSQL is free to compute in whatever order is fastest.
//
Search across all documentation pages
A SQL query looks like a simple sentence, but it is actually a declarative specification of a result set that PostgreSQL is free to compute in whatever order is fastest.
This page builds the mental model for how SQL is read, reordered, and executed by PostgreSQL, which explains why WHERE and HAVING behave differently, why written order is not execution order, and why the same result can come from very different plans.
EXPLAIN tells you the actual physical plan.SQL is a declarative language, which means you describe the shape of the result you want rather than the steps to produce it.
This is the opposite of an imperative language like a loop in application code, where you control every step explicitly.
Because SQL is declarative, PostgreSQL's planner is free to choose scans, joins, and orderings that never appear anywhere in your query text.
SQL's roots are in relational algebra, where a query is a sequence of operations, selection, projection, join, and aggregation, applied to sets of rows called relations.
A table is a relation, a row is a tuple, and a column is an attribute, even though everyday SQL rarely uses that vocabulary.
The clauses you write, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT, map onto that relational algebra even though you type them in a different order than they logically execute.
The logical processing order starts with FROM and any joins, then WHERE filters rows, then GROUP BY collapses them, then HAVING filters groups, then SELECT computes the final columns, then ORDER BY sorts, and finally LIMIT trims the result.
That logical order is why you cannot reference a SELECT alias inside the same query's WHERE clause but you can reference it in ORDER BY, since WHERE runs before SELECT and ORDER BY runs after.
PostgreSQL never actually executes clauses in that logical order literally, since the planner is free to reorder operations as long as the final result matches the logical specification.
written: SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY -> LIMIT
logical: FROM/JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
Seeing both orders side by side explains most of the surprising behavior in SQL, from alias scoping to why a HAVING clause can reference an aggregate that WHERE cannot.
The planner turns your query into a tree of physical operators, deciding things like whether to scan a table sequentially or via an index, and in what order to join multiple tables.
Those decisions are driven by statistics PostgreSQL keeps about table size and column value distribution, which ANALYZE refreshes and autovacuum triggers automatically.
A predicate is called sargable when the planner can use an index to satisfy it directly, and wrapping an indexed column in a function usually breaks that, forcing a full scan instead.
NULL adds a third logical value, unknown, to every comparison, which is why WHERE column = NULL never matches anything and IS NULL exists as a separate operator.
Set operations like UNION, INTERSECT, and EXCEPT combine the results of two queries as whole sets rather than joining them row by row.
Subqueries let you nest one query's logical result inside another, either as a scalar value, a row source in FROM, or a boolean test with EXISTS.
Correlated subqueries reference the outer query's columns and conceptually re-run per outer row, though the planner often rewrites them into a join-like plan internally.
The same logical result can often be expressed as a correlated subquery, a join, a window function, or a common table expression, and PostgreSQL's planner does not always produce the same physical plan for each shape even when the output rows are identical.
Common table expressions defined with WITH acted as an optimization fence before PostgreSQL 12, but from PostgreSQL 12 onward, and still true in 18.4, the planner inlines non-recursive CTEs unless you mark one MATERIALIZED or reference it more than once.
Window functions compute an aggregate-like value per row without collapsing the rows the way GROUP BY does, which makes them the right tool for running totals, rankings, and moving averages.
EXISTS and IN often produce equivalent results for membership tests, but EXISTS short-circuits on the first match and handles NULLs more predictably than NOT IN does.
Query readability and query performance are not the same axis, so refactoring a nested subquery into a CTE for clarity should not be assumed to change the execution plan.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Correlated subquery | Reads naturally top to bottom, good for a single derived value | Can force per-row re-evaluation if the planner can't rewrite it | Small lookups, admin screens |
| JOIN | Lets the planner freely choose join order and algorithm | Duplicates rows on one-to-many relationships unless aggregated | Combining data from multiple tables at scale |
| Window function | Keeps row-level detail while adding aggregate context | Less familiar syntax, easy to misuse frame clauses | Rankings, running totals, per-group comparisons |
| Common table expression (CTE) | Names a step for readability and reuse within one query | No longer guarantees materialization unless requested | Breaking a complex query into readable stages |
Choosing between these shapes should start from what the query needs to express, then be checked against EXPLAIN, not decided by habit alone.
WHERE filters rows before SELECT computes the output columns, which is why a SELECT alias cannot be used inside that same query's WHERE clause.NULL = NULL evaluates to unknown rather than true, and only IS NULL reliably tests for it.GROUP BY is built to pair with aggregate functions per group.Because logically WHERE runs before SELECT computes its output columns, the alias does not exist yet at the point WHERE is evaluated.
FROM/joins, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT, and written order only matches this sequence for FROM and WHERE.
NULL means unknown, comparing anything to unknown yields unknown rather than true, and IS NULL or IS NOT NULL is the correct way to test for it instead.
Not necessarily, since the planner often rewrites correlated subqueries into join-equivalent plans, so the choice should be based on readability and correctness first, then verified with EXPLAIN.
WHERE filters individual rows before grouping happens.
HAVING filters groups after GROUP BY has collapsed rows, so it can reference aggregate functions that WHERE cannot.
Not necessarily, since PostgreSQL 12 inlines non-recursive CTEs into the surrounding query by default unless one is marked MATERIALIZED or referenced more than once.
The planner's choice depends on table statistics, which change as data grows or as ANALYZE runs, so the same SQL text can produce a different plan over time.
A sargable predicate is one the planner can satisfy directly with an index, and wrapping an indexed column in a function or cast usually makes the predicate non-sargable, forcing a full scan instead.
Use a window function when you need an aggregate-style value, like a rank or running total, alongside the original row-level detail instead of collapsing rows.
UNION removes duplicate rows across the combined result, which requires a sort or hash step.
UNION ALL keeps every row and is cheaper when duplicates are not a concern.
EXISTS can short-circuit on the first matching row and handles NULLs in the subquery more predictably than NOT IN, which can silently return no rows if the subquery produces any NULL.
No, since keyword casing, line breaks, and indentation are purely for human readability and have no effect on the parsed query or the chosen plan.
Run EXPLAIN (ANALYZE, BUFFERS) against the query and look for an index scan or index-only scan node instead of a sequential scan.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17); the CTE inlining behavior described applies from PostgreSQL 12 onward.
Reviewed by Chris St. John·Last updated Jul 19, 2026