In SQL, as far as I know, the logical query processing order, which is the conceptual interpretation order, starts with FROM in the following way:

  1. FROM
  2. WHERE

Following this list it's easy to see why you can't have SELECT aliases in a WHERE clause, because the alias hasn't been created yet. T-SQL (SQL Server) follows this strictly and you can't use SELECT aliases until you've passed SELECT.

But in MySQL it's possible to use SELECT aliases in the HAVING clause even though it should (logically) be processed before the SELECT clause. How can this be possible?

To give an example:

SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate) 
HAVING Amount>1;

The statement is invalid in T-SQL (because HAVING is referring to the SELECT alias Amount)...

Msg 207, Level 16, State 1, Line 5
Invalid column name 'Amount'.

...but works just fine in MySQL.

Based upon this, I'm wondering:

  • Is MySQL taking a shortcut in the SQL rules to help the user? Maybe using some kind of pre-analysis?
  • Or is MySQL using a different conceptual interpretation order than the one I though all RDBMS were following?
  • 1
    My guess is, it's your second bullet point.
    – user1822
    Commented Sep 24, 2013 at 8:52
  • 3
    Well I guess it doesn't cause any ambiguity or confusion until they support ranking functions. Then SELECT C, ROW_NUMBER() OVER (ORDER BY X) AS RN FROM T GROUP BY C HAVING RN = 1 will be problematic as the ROW_NUMBER runs after the HAVING Commented Sep 24, 2013 at 9:04
  • I'm not sure what ranking functions that are supported by MySQL. If you want the row number you have to create it in this way: SELECT @rownum:=@rownum + 1 as row .... Maybe the reason why they support SELECT aliases simply is because they can, due to the fact that they don't support things that would make it impossible...who knows? :)
    – Ohlin
    Commented Sep 24, 2013 at 9:14
  • As @MartinSmith explains, as long as there are no window/ranking functions, the logical order of execution for HAVING and SELECT clause can be interchanged. So, there is no ambiguity in doing this and can simplify the looks of the code when there are monstrous expressions in SELECT. Commented Sep 24, 2013 at 11:50
  • Hopefully this is somewhat on-topic to say I answered a question Here that is enjoying faster results (with distincts ) ... with the Alias in the Having despite the same Explain output. So some variation with the Optimizer is happening.
    – Drew
    Commented Jul 4, 2016 at 18:06

2 Answers 2


Well when you have a question of this sort the best source of information IMHO is MySQL documentation. Now to the point. This is the behavior of MySql extension to GROUP BY which is enabled by default.

MySQL Extensions to GROUP BY
MySQL extends this behavior to permit the use of an alias in the HAVING clause for the aggregated column

If you want standard behavior you can disable this extension with sql_mode ONLY_FULL_GROUP_BY


If you try to execute the above-mentioned query in ONLY_FULL_GROUP_BY sql_mode you'll get the following error message:

Non-grouping field 'Amount' is used in HAVING clause: SELECT YEAR(orderdate), COUNT(*) as Amount FROM Orders GROUP BY YEAR(orderdate) HAVING Amount > 1

Here is SQLFiddle demo

Therefore it's up to you how to configure and use your instance of MySQL.

  • You're absolutely right about the documentation. I just never thought it could be so clearly written as you quoted it above :) Thanks for finding it...
    – Ohlin
    Commented Sep 25, 2013 at 6:12
  • This answer doesn't answer "Is MySQL doing pre-analysis or is MySQL using a different conceptual interpretation?".
    – Pacerier
    Commented May 8, 2015 at 5:03
  • 2
    @Pacerier MySQL is "doing pre-analysis," of course, because the query optimizer considers all facets of the query while choosing what it believes will be the best query plan. The notion of a "different conceptual interpretation" betrays a misunderstanding of the fact that the server is free to implement the conceptual model in any way that produces a valid result. ORDER BY, for example, might be actually handled much earlier than it theoretically is, if the optimizer finds that rows can be initially read in order from an index that's already in the desired order. Commented Feb 20, 2016 at 12:36

Good question.

I think you should run these querys

EXPLAIN SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate) 
HAVING Amount>1;

and check how the query is rewritten. iam pretty sure the query optimizer replace Amount with COUNT(*)

SELECT YEAR(orderdate), COUNT(*) as Amount
FROM Sales.Orders
GROUP BY YEAR(orderdate) 

Like it does with

 id = 5 - 3

after query optimizer its something like this.

 test.id as 'id'
 test.id = 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.