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.
Specifies the table(s) that are of interest. Tables will be joined if there are
Filters the rows based on a specified condition.
3. GROUP BY
Groups the rows by one or more column values.
Filters the groups based on a specified condition.
Specifies the columns that you’re interested in.
Removes duplicate rows.
7. ORDER BY
Orders results by some column value in ascending or descending order.
Limits the number of results or skip some number of results.
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:
- FROM: Join the
- WHERE: skipped.
- GROUP BY: Iterate over all the rows and group them by the
department.dept_namecolumn 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_namecolumn values there are.
- HAVING: skipped.
- SELECT: Select the
d.dept_name, evaluate the
count(*)aggregate function and returns its results as
- DISTINCT: skipped.
- ORDER BY: order the rows by the
num_employeesalias in descending ordr.
- LIMIT/OFFSET: skipped.
- Knowing which clauses are executed first by MySQL will help you troubleshoot issues like referencing column aliases in
SELECTclauses are executed after
WHEREclauses so aliases won’t be available in