A SQL query reads like an English sentence: choose these columns, from this table, where this is true. The database does not process it in that order. It works out the rows first, filters them, groups them, filters the groups, and only then works out which columns to show you. Once you hold that logical order in your head, a whole family of puzzling errors and wrong results stops being puzzling.

The logical processing order

Standard SQL defines a logical sequence for the clauses of a single query. Real engines are free to execute things differently for speed, but the result must be the same as if they had followed this sequence:

  1. FROM and any joins: build the working set of rows.
  2. WHERE: throw away individual rows that fail the condition.
  3. GROUP BY: collapse the surviving rows into groups.
  4. HAVING: throw away whole groups that fail a condition.
  5. The column list (the SELECT clause): compute output expressions and assign aliases. Window functions are evaluated here too.
  6. DISTINCT: remove duplicate output rows.
  7. ORDER BY: sort the result.
  8. LIMIT, TOP or FETCH FIRST: keep only the first N rows.

The word “logical” matters. A query optimiser may push a filter down into a join, use an index to avoid sorting, or stop reading early when there is a limit. You should never rely on physical order to reason about results. You should rely on the logical order, because it decides what each clause is allowed to see.

A small table to reason with

Take an orders table with six rows across three regions:

RegionOrder amount (₹)
North400
North900
South1,200
South300
West650
West200

Suppose you want total sales per region. Grouping by region and summing the amount gives North ₹1,300, South ₹1,500 and West ₹850.

Now two different requests that sound similar. First: “total per region, counting only orders above ₹500”. That is a row-level condition, so it belongs in WHERE. The ₹400, ₹300 and ₹200 orders vanish before grouping, and the totals become North ₹900, South ₹1,200, West ₹650.

Second: “regions whose total is above ₹1,000”. That condition is about a group, and the group total does not exist until step 3 has run. So it belongs in HAVING. The answer is North (₹1,300) and South (₹1,500); West drops out with ₹850.

The two filters answer different business questions. Mixing them up does not always throw an error; sometimes it just quietly answers the wrong question.

Errors that the order explains

“Aggregate functions are not allowed in WHERE.” When the WHERE clause runs, the database is still looking at individual rows. There is no group yet, so SUM(amount) has nothing to sum. Move the condition to HAVING, or compute the aggregate in a subquery or CTE and filter the outer query.

“Column alias not found” in WHERE. If you write amount * 0.18 AS gst in the column list and then try to filter on gst in WHERE, most databases refuse. The alias is created at step 5, and WHERE ran at step 2. Repeat the expression, or wrap the query and filter on the alias from outside.

Aliases do work in ORDER BY. Sorting happens after the column list is computed, so an alias is visible there. This is the same rule seen from the other side, and it is why the alias trick works in one clause and fails in another.

“Column must appear in the GROUP BY clause or be used in an aggregate function.” After grouping, each output row represents many input rows. If you ask for the customer name while grouping only by region, the database cannot know which of the many names to show. PostgreSQL, SQL Server and Oracle reject this. MySQL with the ONLY_FULL_GROUP_BY mode switched off will pick a value without telling you which one, which is worse than an error because the result looks fine.

Where dialects bend the rule

The standard order is a good mental model, but vendors add conveniences. MySQL lets you refer to a column alias inside HAVING. BigQuery and several other engines accept a column alias in GROUP BY, and Snowflake and DuckDB go further and accept one in WHERE as well. Snowflake, BigQuery and Databricks also support QUALIFY, a clause that filters on window function results and logically sits after the window functions are computed.

These shortcuts are handy, but queries that depend on them break when moved to another database. If your team runs a mix, say PostgreSQL for the application and Snowflake for the warehouse, write the portable version unless you are sure the query will stay where it is.

Window functions and the “filter by rank” trap

Window functions such as ROW_NUMBER and RANK are computed in step 5, after WHERE, GROUP BY and HAVING have all finished. Two consequences follow.

First, you cannot filter on a window result in WHERE of the same query. To keep the top three products per region, compute the rank in an inner query or CTE, then filter on it outside (or use QUALIFY where available).

Second, the window only sees rows that survived earlier filters. If you filter to orders above ₹500 in WHERE and then compute each order’s share of the regional total with a windowed sum, the “total” excludes the small orders. Sometimes that is what you want. Often it is not, and the percentages add up to 100% of the wrong base.

ON versus WHERE in an outer join

Because joins happen in step 1 and WHERE in step 2, the placement of a condition in a LEFT JOIN changes the result. Imagine customers left-joined to orders, where you want only orders from 2025.

  • Put the year condition in the ON clause, and the join matches only 2025 orders. Customers with no 2025 orders still appear, with empty order columns. You keep every customer.
  • Put the same condition in WHERE, and it runs after the join. For customers with no matching order the order date is NULL, the condition is not true, and those customers are removed. Your left join has silently become an inner join.

For inner joins the two placements give the same result, which is why the difference catches people out when they switch join types.

Using the order as a debugging habit

When a query returns a number you do not trust, walk it in logical order. Run just the joins and count rows: did a join multiply them? Add the row filters and count again. Add the grouping and look at a few groups. Add the group filter. Only then look at the computed columns. Each step has one job, and checking them one at a time is far faster than staring at the final output and guessing.

The same thinking also makes queries clearer and often cheaper. A condition that can be applied to individual rows belongs in WHERE, not HAVING, because filtering before grouping means fewer rows to group. Some optimisers, PostgreSQL’s among them, will move a simple HAVING condition on a grouping column into the row filter for you, but not every engine does, and writing it in the right place states your intent plainly either way.

Written by the RP Strativa Team. Figures in the examples are illustrative.

Leave a Reply

Trending