SQL Logical Order — Describe the logical order of SQL operations (FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT) (conceptual)

When Edgar F. Codd published his seminal 1970 paper on the relational model of data, he envisioned a world where users could describe what data they wanted rather than how to retrieve it. This declarative philosophy became the foundation of SQL (Structured Query Language), which was first implemented at IBM as SEQUEL in the mid-1970s. A critical implication of this declarative design is that the order in which a programmer writes SQL clauses is not the order in which the database engine evaluates them. The engine follows a well-defined logical processing order that determines which rows are considered, how they are grouped, and what ultimately appears in the result set.

The fundamental question this lesson addresses is deceptively simple: in what order does the database engine logically process the clauses of a SQL query? The syntactic order—SELECT first, then FROM, then WHERE—is a notational convention for human readability. The logical order starts with FROM, then WHERE, and evaluates SELECT near the end. Failing to understand this distinction leads to common errors: referencing column aliases in the WHERE clause, misusing aggregate functions, or being confused by the behavior of HAVING versus WHERE. Mastering the logical order transforms SQL from a trial-and-error exercise into a principled, predictable tool.

Before dissecting each phase, it is essential to establish the conceptual framework that governs SQL query evaluation. SQL is a declarative language, meaning you specify the desired result rather than the procedural steps to compute it. The database management system (DBMS) translates your declaration into a physical execution plan, but the logical processing order defines the conceptual sequence of operations that guarantees the result's correctness. Every SQL engine—PostgreSQL, MySQL, SQL Server, Oracle—must produce results consistent with this logical order, even though its optimizer may physically execute steps in a completely different sequence for performance.

The diagram above illustrates the central insight of this lesson. When you write a SQL query, you begin with the SELECT keyword, but the engine begins its logical evaluation with FROM. Each phase transforms an intermediate virtual table into a new virtual table, passing it down the pipeline. The FROM clause constructs the broadest possible set of candidate rows (the Cartesian product of all joined tables, refined by join conditions). The WHERE clause then eliminates rows that fail the filter predicate. GROUP BY collapses the surviving rows into groups, after which HAVING filters entire groups. Only then does SELECT evaluate expressions and assign aliases. Finally, ORDER BY sorts the result and LIMIT truncates it.

Phase 1: FROM / JOIN

The FROM clause is the logical starting point of every query. When multiple tables are specified, the engine conceptually forms their Cartesian product—every combination of rows from each table. If table A has m rows and table B has n rows, the Cartesian product contains m × n rows. JOIN conditions (ON clauses) then filter this product to retain only meaningful row pairings. OUTER JOINs additionally preserve unmatched rows from one or both sides, padding missing columns with NULLs. The output of this phase is a single virtual table that contains every column from every source table.

Phase 2: WHERE

The WHERE clause applies a Boolean predicate to each individual row in the virtual table produced by FROM. Rows for which the predicate evaluates to TRUE survive; rows evaluating to FALSE or UNKNOWN (due to NULLs) are discarded. A critical rule is that aggregate functions cannot appear in WHERE because groups have not yet been formed. You cannot write WHERE COUNT(*) > 5 — the engine has no concept of groups at this stage. This restriction is a direct consequence of the logical order.

Phase 3: GROUP BY

The GROUP BY clause partitions the filtered rows into groups based on one or more grouping columns. Within each group, all rows share identical values for the grouping columns. Once grouping occurs, the virtual table transitions from a table of individual rows to a table of groups. From this point forward, any column referenced in SELECT or HAVING must either appear in the GROUP BY list or be wrapped in an aggregate function (SUM, COUNT, AVG, MAX, MIN, etc.). This constraint ensures that each expression produces exactly one value per group.

Phase 4: HAVING

The HAVING clause is the group-level counterpart of WHERE. It applies a Boolean predicate to each group, and groups that fail are eliminated. Unlike WHERE, HAVING can reference aggregate functions because groups already exist at this stage. For example, HAVING COUNT(*) > 5 retains only groups with more than five members. A common beginner mistake is using HAVING for row-level filters — this is semantically incorrect and often less efficient than using WHERE, because HAVING runs after grouping has already been performed.

Phase 5: SELECT

The SELECT clause is evaluated fifth, despite appearing first in the syntax. It determines which columns or expressions appear in the output. Column aliases are defined here via AS, which is precisely why those aliases are invisible to WHERE, GROUP BY, and HAVING—those clauses have already been evaluated. However, aliases defined in SELECT are visible to ORDER BY and LIMIT, which run later. If DISTINCT is specified, duplicate rows are eliminated at this stage.

Phase 6: ORDER BY

The ORDER BY clause sorts the result set. Because it executes after SELECT, it can reference column aliases and ordinal positions (e.g., ORDER BY 2 refers to the second column in the SELECT list). ORDER BY is the only clause that can reference columns not in the SELECT list (in most SQL dialects), because the full virtual table is still available from earlier phases. Without ORDER BY, SQL makes no guarantees about row order; the result is technically an unordered set.

Phase 7: LIMIT / OFFSET

The LIMIT clause (called TOP in SQL Server or FETCH FIRST in ANSI SQL:2008) truncates the sorted result set to a specified number of rows. OFFSET, when used alongside LIMIT, skips a given number of rows before returning results, enabling pagination. Because LIMIT is the final logical phase, it operates on the fully sorted, fully projected result. Using LIMIT without ORDER BY is generally meaningless, since the set of rows retained is nondeterministic.

One of the most practical consequences of the logical order is the visibility of column aliases. Since SELECT is evaluated in phase 5, any alias you create there (for example, SELECT price * quantity AS total_cost) does not exist during earlier phases. This explains why the following query is illegal in standard SQL: SELECT price * quantity AS total_cost FROM orders WHERE total_cost > 100. The WHERE clause (phase 2) cannot see total_cost because it hasn't been defined yet. You must instead repeat the expression: WHERE price * quantity > 100.

Consider the following query against a hypothetical orders table with columns customer_id, product_category, amount, and order_date:

Understanding the logical order is not merely academic; it directly prevents a class of common SQL bugs and performance anti-patterns. The table below catalogs the most frequent mistakes that arise from ignoring or misunderstanding the processing sequence.

The logical order establishes correctness, but real-world database engines employ query optimizers that rewrite and reorder operations for efficiency. A cost-based optimizer might push a WHERE predicate into a JOIN condition (predicate pushdown), evaluate LIMIT before fully sorting (top-N optimization), or skip GROUP BY entirely when an index already provides sorted, aggregated data. The guarantee is that the final result is indistinguishable from what the logical order would produce. Understanding the logical order thus gives you the mental model to predict what the optimizer is allowed to do and—crucially—what errors it cannot silently fix.

As you advance into topics like window functions, recursive CTEs, and query execution plans (EXPLAIN), the logical order remains your foundational mental model. Window functions, for example, are logically evaluated after WHERE and GROUP BY but before ORDER BY—which is why they can reference columns filtered by WHERE and aggregated by GROUP BY, but not values sorted by ORDER BY. Every new SQL feature slots into this pipeline; knowing the pipeline means you can reason about any query, no matter how complex.