SQL: Logical processing order

In a nutshell

Similar to how there is a pre-defined order for performing math operations (BIDMAS), there is also an order in which clauses in a SQL query are evaluated. Understanding this order will help you troubleshoot your SQL queries.

The order

1. FROM/JOIN

Specifies the table(s) that are of interest. Tables will be joined if there are JOIN statements.

2. WHERE

Filters the rows based on a specified condition.

3. GROUP BY

Groups the rows by one or more column values.

4. HAVING

Filters the groups based on a specified condition.

5. SELECT

Specifies the columns that you’re interested in.

6. DISTINCT

Removes duplicate rows.

7. ORDER BY

Orders results by some column value in ascending or descending order.

8. LIMIT/OFFSET

Limits the number of results or skip some number of results.

Example

select d.dept_name, count(*) as num_employees
from departments d
         inner join dept_emp de on de.dept_no = d.dept_no
group by d.dept_name
order by num_employees desc

SQL will process the above query as follows:

1. FROM: Join the department and dept_emp tables.
2. WHERE: skipped.
3. GROUP BY: Iterate over all the rows and group them by the department.dept_name column value. At the end of this iteration, we will have a reduced row set that will be equal in length to however many unique department.dept_name column values there are.
4. HAVING: skipped.
5. SELECT: Select the d.dept_name, evaluate the count(*) aggregate function and returns its results as num_employees.
6. DISTINCT: skipped.
7. ORDER BY: order the rows by the num_employees alias in descending ordr.
8. LIMIT/OFFSET: skipped.

Other notes

• Knowing which clauses are executed first by MySQL will help you troubleshoot issues like referencing column aliases in WHERE clauses. SELECT clauses are executed after WHERE clauses so aliases won’t be available in WHERE clauses.

Sources

• A Look Into SQL’s Order Of Execution – Aashish Nair