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.

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.

Summary

Core Idea: A SQL query is a declarative description of a result set, evaluated by PostgreSQL in a fixed logical order that differs from the order you type the clauses.
Why It Matters: Knowing the logical order explains alias scoping, NULL comparison surprises, and why WHERE and HAVING are not interchangeable.
Key Concepts: declarative language, logical processing order, sargable predicate, three-valued logic, cost-based planner.
When to Use: Reach for this model whenever a query's behavior surprises you, or before choosing between a subquery, join, window function, or CTE.
Limitations / Trade-offs: Understanding logical order explains correctness questions but not performance; only EXPLAIN tells you the actual physical plan.
Related Topics: join types, NULL semantics, subqueries and EXISTS, set operations, aggregates and GROUP BY.

Foundations

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.

Mechanics & Interactions

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.

Advanced Considerations & Applications

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.

Common Misconceptions

SQL executes top to bottom as written - the planner is free to reorder operations as long as the logical result matches, so written order and execution order are different things.
SELECT runs before WHERE - logically 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 equals NULL - NULL represents unknown, so NULL = NULL evaluates to unknown rather than true, and only IS NULL reliably tests for it.
A subquery is always slower than a JOIN - the planner frequently rewrites subqueries into join-equivalent plans internally, so the written shape does not determine the physical plan.
DISTINCT and GROUP BY are interchangeable - both can deduplicate rows, but only GROUP BY is built to pair with aggregate functions per group.

FAQs

Why doesn't my WHERE clause see the alias I defined in SELECT?

Because logically WHERE runs before SELECT computes its output columns, the alias does not exist yet at the point WHERE is evaluated.

What is the actual logical order SQL executes in?

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.

Why does `WHERE column = NULL` never return rows?

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.

Is a JOIN always faster than a subquery?

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.

What's the difference between WHERE and HAVING?

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.

Do CTEs always run before the rest of the query?

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.

Why does the same query sometimes get a different execution plan?

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.

What does "sargable" mean and why does it matter?

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.

When should I reach for a window function instead of GROUP BY?

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.

What's the difference between UNION and UNION ALL?

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.

Why does EXISTS sometimes outperform IN?

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.

Does formatting my SQL affect performance?

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.

How do I know if PostgreSQL is actually using my index?

Run EXPLAIN (ANALYZE, BUFFERS) against the query and look for an index scan or index-only scan node instead of a sequential scan.

SQL Querying Basics - hands-on SELECT/WHERE/ORDER BY examples
Join Types - how FROM-clause joins combine relations
Null Semantics - three-valued logic in depth
Subqueries and EXISTS - nested query patterns
Aggregates and GROUP BY - grouping and aggregate functions
UNION, INTERSECT, EXCEPT - set operations between queries

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17); the CTE inlining behavior described applies from PostgreSQL 12 onward.