SQL’s Order Of Execution

Hi Ramsey!
Thanks for reaching out.

The order MySQL executes the statements is the following:
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
ORDER BY clause

SELECT clause

This is important for understanding how queries are parsed. You cannot use a column alias defined in a SELECT in the WHERE clause, for instance, because the WHERE is parsed before the SELECT. On the other hand, such an alias can be in the GROUP BY, HAVING and ORDER BY clause.

Hope this helps.
Best,
Tsvetelin

Your parsing order appears to be different from what I was taught. Do you have a source to back up your statement? I'm curious if the order of parsing differs between RDBMS.

Hi Ramsey and Tsvetelin!

I hope you don't mind if I join the conversation.

@Ramsey: Can you please share the order you are referring to? Which steps are the ones that seem incorrect/strange to you and which version of the SQL language (i.e. database) are you referring to?
The order Tsvetelin has provided certainly refers to the way the Optimizer works in MySQL.

Looking forward to your reply.
Kind regards,
Martin

Well, I was taught (example book: SQL Pocket Guide, 3rd Edition published by O'REILLY) , the order of execution in SQL is:

FROM (and JOINs) clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT (and DISTINCT) clause
ORDER BY clause
LIMIT clause

I just don't get how a SELECT clause can be parsed before, for example, grouping the rows.

Thank you
Ramsey

Hi Ramsey!

Please accept my apologies on the late answer as well as incorrectly pointing the order to you.

The order is as you say. Just regarding the alias, an alias can be used in a HAVING clause if and only if it has been defined earlier, e.g. in the field list (i.e. just after the SELECT keyword). In MySQL, we don't assign aliases in the HAVING clause.

For instance, the following query is executable:

SELECT 
    emp_no, AVG(salary)
FROM
    salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000
ORDER BY emp_no;

While the following one isn't:

SELECT 
    emp_no, AVG(salary)
FROM
    salaries
GROUP BY emp_no
HAVING AVG(salary) AS avg_salary > 120000
ORDER BY emp_no;

Instead, this one would work too, since the alias has been assigned in the field list:

SELECT 
    d.dept_name, AVG(salary) AS average_salary
FROM
    departments d
        JOIN
    dept_manager m ON d.dept_no = m.dept_no
        JOIN
    salaries s ON m.emp_no = s.emp_no
GROUP BY dept_name
HAVING average_salary > 60000
ORDER BY average_salary DESC
;

Hope this helps but please feel free to get back to us should you need further assistance. Thank you.
Best,
Martin