Baldwin Apps

Posted on Jun 23

SQL Pattern Series #8: The Query Order Pattern

#beginners #database #sql #tutorial

Understanding the order SQL actually processes a query

SQL Pattern Series #8 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

Why SQL does not run strictly from top to bottom
The difference between written order and logical processing order
Why WHERE filters rows before grouping
Why HAVING filters groups after aggregation

SQL queries are usually written like this:

SELECT
    CustomerID,
    COUNT(*) AS OrderCount
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerID
HAVING COUNT(*) > 5
ORDER BY OrderCount DESC;

That looks like the order SQL runs in.

But logically, SQL processes the query in a different sequence.

That difference explains a lot of beginner confusion.

The Problem

Many SQL developers first read queries from top to bottom.

That makes sense visually.

The query starts with:

SELECT

So it feels like SELECT must happen first.

But SQL is declarative.

You describe the result you want.

The database decides how to produce it.

Logical processing order helps you understand why certain clauses behave the way they do.

The Query Order Pattern

The Query Order Pattern is the habit of thinking about SQL in logical stages.

A simplified logical order is:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

This is not necessarily the physical execution plan.

The optimizer may rearrange operations internally.

But this logical order explains how the query is interpreted.

FROM: Choose the Data Source

SQL first determines where the data comes from.

FROM Orders

This establishes the table or joined result set that the rest of the query will operate on.

Before rows can be filtered, grouped, or selected, SQL needs a source.

WHERE: Filter Rows

Next, WHERE filters individual rows.

WHERE OrderDate >= '2024-01-01'

This happens before grouping.

That means WHERE cannot filter aggregate values like:

COUNT(*)

because those counts do not exist yet.

WHERE works row by row.

GROUP BY: Create Groups

After row filtering, SQL groups the remaining rows.

GROUP BY CustomerID

Rows with the same CustomerID are collected into groups.

Once groups exist, aggregate functions such as:

COUNT(*)
SUM(amount)
AVG(score)

can be calculated.

HAVING: Filter Groups

HAVING filters groups after aggregation.

HAVING COUNT(*) > 5

This is why HAVING can use aggregate expressions.

At this point, SQL has already grouped the rows and calculated the count for each group.

A useful shortcut:

WHERE filters rows.

HAVING filters groups.

SELECT: Choose the Output

After filtering and grouping, SQL determines which expressions appear in the result.

SELECT
    CustomerID,
    COUNT(*) AS OrderCount

This is where output columns and aliases are produced.

That is why aliases created in SELECT are not always available in earlier clauses such as WHERE.

The alias may not exist yet in the logical processing order.

ORDER BY: Sort the Result

Finally, ORDER BY sorts the result set.

ORDER BY OrderCount DESC;

Unlike WHERE, many database systems allow ORDER BY to reference aliases created in SELECT.

By this point, the output result has been formed, so the alias is available for sorting.

Why This Pattern Matters

Understanding query order helps explain many common SQL surprises:

Why WHERE COUNT(*) > 5 does not work
Why HAVING COUNT(*) > 5 does work
Why some aliases are not available in WHERE
Why filters before grouping affect the final aggregates
Why SQL feels different from procedural programming

Once this pattern clicks, many SQL errors become easier to diagnose.

Written Order vs Logical Order

SQL is written in one order:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But it is logically interpreted in another:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

This does not mean the database literally executes every query in that exact sequence.

Modern optimizers are far more sophisticated than that.

But the logical order is still useful because it explains the meaning of the query.

When I Reach for This Pattern

I think about the Query Order Pattern when:

a query fails because an alias is not recognized
WHERE and HAVING become confusing
aggregate filters are involved
reports return unexpected totals
learners try to read SQL from top to bottom

Examples include:

filtering grouped reports
calculating counts by customer
filtering sales totals
debugging aggregate queries
explaining why SQL clauses behave differently

Key Takeaway

SQL is written in one order, but understood in stages.

The most useful shortcut is:

WHERE filters rows.

HAVING filters groups.

Once you understand that sequence, many SQL queries become easier to read, debug, and explain.