Master SQL Execution Order: From FROM to LIMIT Explained

Hello, I am Feng Ge.

Standard query example:

Actual SQL execution order:

First execute FROM and JOIN to determine table relationships and obtain preliminary data.

WHERE performs an initial filter on the data.

GROUP BY groups the data.

HAVING applies ordinary or aggregate filtering to each group.

SELECT retrieves the required fields; if aggregate functions are used, a new column is added.

DISTINCT removes duplicate rows.

Finally, ORDER BY sorts the result set according to the specified criteria.

Data Association Process

Two tables in the database

FROM & JOIN & WHERE

These clauses determine the scope of tables involved in the query and the join conditions. from table1 join table2 on table1.id=table2.id When selecting multiple tables, WHERE can be used for join conditions. from table1, table2 where table1.id=table2.id If no join condition is provided, a Cartesian product occurs.

GROUP BY

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

For example, grouping by the parity of an ID.

HAVING & WHERE

HAVING can contain ordinary conditions or aggregate functions, while WHERE is limited to ordinary expressions. Often HAVING can replace WHERE for a cleaner query.

Using WHERE then GROUP BY

First filter out rows that do not satisfy WHERE, then perform grouping.

Using GROUP BY then HAVING

First group the data, then filter groups with HAVING; the result is effectively the same.

100/2=50, splitting 100 as (10+10+10+10+10…)/2=5+5+5+…+5=50; as long as the filter condition remains unchanged, grouping does not affect the outcome.

HAVING differs from WHERE because it supports aggregate functions, allowing conditions such as filtering rows where salary is less than the average salary of each group.

having salary<avg(salary)

SELECT

After grouping, SELECT retrieves fields; aggregate functions add a new column. 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)

ORDER BY

Finally, ORDER BY sorts the result set, e.g., by ID. If LIMIT is present, the engine stops after retrieving the required number of rows.

LIMIT

LIMIT is applied after sorting; placing it earlier can yield incorrect results. For example, to get the three smallest salaries, LIMIT must be applied after ORDER BY.