Understanding the Execution Order of SQL Queries

This article explains the logical execution sequence of a typical SQL query, detailing each phase from FROM/JOIN/WHERE through GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT, and illustrating the process with diagrams and code examples.

The actual SQL execution order is:

First, FROM and JOIN are processed to determine the relationship between tables and produce an initial data set.

Next, WHERE applies simple filtering to that data.

Then GROUP BY groups the rows according to the specified criteria.

Each group is evaluated by HAVING , which can contain ordinary conditions or aggregate‑function filters.

After that, SELECT retrieves the required columns; if aggregate functions are used, they add new fields to the result set.

DISTINCT removes duplicate rows.

Finally, the grouped results are merged and sorted according to ORDER BY .

Data Association Process

Two tables in a database are shown.

FROM & JOIN & WHERE

These clauses determine which tables are involved and how they are related.

Example of joining two tables:

from table1 join table2 on table1.id=table2.id

Example of using a comma list with a WHERE condition (produces a Cartesian product if no condition is given):

from table1, table2 where table1.id=table2.id

GROUP BY

GROUP BY partitions the data according to the specified grouping criteria but does not filter rows.

For example, grouping by the parity of an ID.

HAVING & WHERE

HAVING can contain ordinary conditions as well as aggregate functions, while WHERE can only contain ordinary expressions. Often, HAVING can replace WHERE for a smoother‑looking query.

Using WHERE before GROUP BY

First remove rows that do not satisfy the WHERE condition, then group.

Using GROUP BY before HAVING

First group, then filter groups with HAVING; the result is effectively the same in most cases.

Example illustrating the principle:

100/2=50 , which can be expressed as (10+10+10+10+10…)/2=5+5+5+…+5=50 . The grouping does not change the outcome as long as the filter criteria remain unchanged.

HAVING differs because it supports aggregate functions, e.g., filtering each group by the average salary:

Current data grouping:

having salary<avg(salary)

SELECT

After grouping, SELECT retrieves the required columns; aggregate functions add a new field to the result set. Duplicate column names must be qualified to avoid ambiguity, and DISTINCT can be used to remove duplicate rows.

select employee.id, distinct name, salary, avg(salary)

The data from each group (after HAVING) is then merged.

ORDER BY

Finally, ORDER BY sorts the result set according to the specified criteria, such as sorting by ID.

LIMIT

LIMIT is applied after sorting; it restricts the number of rows returned. Placing LIMIT before ORDER BY can yield incorrect results because the subset is taken before sorting.

Source: blog.csdn.net/weixin_44141495/article/details/108744720