SQL Execution Order

SQL queries are processed in a specific, logical order that often differs from the linear order in which you write them. Understanding this execution order is crucial for writing efficient and correct queries, especially when encountering unexpected errors related to aliases or filtering.

How SQL Execution Works

When you submit a SQL query, the database engine doesn't simply read it from top to bottom. Instead, it follows a defined sequence of operations to build the result set. This sequence ensures data integrity and allows for complex aggregations and filtering.

1. FROM and JOIN: The very first step is to identify the tables involved (`FROM`) and establish how they relate to each other (`JOIN`). The database engine constructs an initial virtual table by combining data from all specified sources.

2. WHERE: Next, the `WHERE` clause acts as the primary row filter. It evaluates conditions on individual rows in the virtual table created by `FROM`/`JOIN` and discards any rows that do not meet the specified criteria. Crucially, at this stage, any column aliases defined in the `SELECT` clause do not yet exist.

3. GROUP BY: After row-level filtering, the `GROUP BY` clause aggregates the remaining rows into summary groups based on one or more columns. Individual rows are collapsed into groups, and aggregate functions (like `SUM`, `COUNT`, `AVG`) can now be applied to these groups.

4. HAVING: The `HAVING` clause then filters these newly formed groups. Similar to `WHERE`, it applies conditions, but it operates on the results of `GROUP BY` and can reference aggregate functions. For example, you might filter for groups where the `COUNT` of items is greater than a certain number.

5. SELECT: Only after all the filtering and grouping is complete does the `SELECT` clause execute. It determines which columns (or expressions) to display from the processed data and applies any specified aliases. This is why aliases defined here cannot be used in `WHERE` — they simply haven't been created yet.

6. ORDER BY: Finally, the `ORDER BY` clause sorts the resulting rows based on specified columns, arranging them in ascending or descending order.

7. LIMIT/OFFSET: The very last step is to restrict the number of rows returned using `LIMIT` (or `TOP` in some SQL dialects) and `OFFSET`, which are purely for presentation.

Logical SQL Execution Order:
1. FROM / JOINs
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY
7. LIMIT / OFFSET

Key Takeaways

• SQL is processed logically, not in the order it's written.
• `FROM` and `JOIN` establish the dataset first.
• `WHERE` filters individual rows *before* `SELECT` aliases are created.
• `GROUP BY` aggregates rows, and `HAVING` filters these aggregated groups.
• `SELECT` is where aliases are defined and columns are chosen, running late in the process.