Understanding SQL Execution Order: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT

The article begins by presenting a standard query and then outlines the actual logical execution sequence of SQL statements.

1. FROM & JOIN – Determines the tables involved and establishes their relationships, producing an initial data set. Example:

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

When multiple tables are listed, the where clause can be used to define join conditions, otherwise a Cartesian product occurs.

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

2. WHERE – Performs basic filtering on the rows produced by the FROM/JOIN step.

3. GROUP BY – Groups the filtered rows according to the specified columns without yet applying any filtering.

4. HAVING – Applies filtering after grouping; it can use aggregate functions (e.g., having salary < avg(salary) ) whereas where cannot.

5. SELECT – Retrieves the required columns; if aggregate functions are used, they generate additional result columns. Duplicate column names must be qualified to avoid ambiguity, and distinct can be applied to remove duplicate rows.

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

6. DISTINCT – Removes duplicate rows from the result set.

7. ORDER BY – Sorts the final result set according to the specified ordering criteria (e.g., by id ).

8. LIMIT – Restricts the number of rows returned and is applied after ordering; placing it earlier would yield incorrect subsets.

The article also includes visual diagrams illustrating each step of the data flow and highlights that the logical order differs from the textual order in which clauses appear in a SQL statement.