MySQL Order of Operations?

Question

Hi All!

Is there a definitive place to find order of operations for mySQL statements? Is the below order correct?

FROM clause WHERE clause GROUP BY clause HAVING clause SELECT clause ORDER BY clause

If this is the case, can I use a term defined in the SELECT clase (select first_name as "f_name") within the group by clause?

Thanks!

Answer 1

I assume you are interested in SELECT, according to the MySQL documentation the syntax is as follows

SELECT
[ALL | DISTINCT | DISTINCTROW ]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr [, select_expr ...]
[FROM table_references
[WHERE where_condition]
[GROUP BY {col_name | expr | position}
  [ASC | DESC], ... [WITH ROLLUP]]
[HAVING where_condition]
[ORDER BY {col_name | expr | position}
  [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
[PROCEDURE procedure_name(argument_list)]
[INTO OUTFILE 'file_name' export_options
  | INTO DUMPFILE 'file_name'
  | INTO var_name [, var_name]]
[FOR UPDATE | LOCK IN SHARE MODE]]

Yes, you can use columns defined with AS in the GROUP BY clause.

Answer 2

I think Order By comes before Select (you can sort on a column you haven't selected), but Group By and Having come dead last for sure. Grouping requires knowledge of the entire result set, and you can only group on non-aggregate columns in the Select list (and you must group on ALL of them). Once the result set is grouped, Having is applied as a set of conditions to the grouped result set.

So, I think most engines, including MySQL, process as follows:

• Define domain source (FROM clause including JOINs)
• Filter (WHERE)
• Sort (ORDER BY)
• Project (SELECT)
• Group (GROUP BY)
• Filter again (HAVING)