Why is it not possible to use an "alias" in the WHERE clause in Mysql?

Asked

Viewed 922 times

3

I need to wear a alias in the clause WHERE, but keeps telling me it’s an "unknown column".

I need to select records that have a higher rating than X. The rating is calculated as the following alias:

sum(reviews.rev_avaliacao)/count(reviews.rev_id) as avg_avaliacao

But in this way it results the error quoted above:

WHERE avg_avaliacao > 10

Already with the use of the HAVING it is possible to circumvent this situation:

HAVING avg_avaliacao > 10

Why is that possible?

mysql sql where

  • Related: https://answall.com/q/211794/8063

  • I believe it is even duplicate, because Maniero’s answer answers this question (not explicitly). where is applied directly on each record, not possessing the column in question; while having is applied on the result, already existing the spine defined by alias.

  • I read this discussion, if you notice, at least at the time they say that the HAVING is usually used with a GROUP BY and without it, the HAVING would basically perform the function of a WHERE

  • 1

    @Andersoncarloswoss from what I understand he wants to know why, and there is no.

  • I believe that it is not a good practice to use the HAVING without the GROUP BY but anyway it works(but it may not be right), just wanted to know exactly why. @Andersoncarloswoss understood, I wanted to know exactly this, if it is indeed what happens.

  • @Phpatrick But you are using aggregation functions to add and count, you are not using the GROUP BY?

  • @Andersoncarloswoss I’m not using the GROUP BY in Mysql this is possible

  • To complement better I am using as a basis another question of Soen, however there the focus is another and they do not explain why with the HAVING you can do that. Soen Question: https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql

Show 3 more comments

1 answer

3


The alias is created during the execution of the SELECT. Contrary to what may seem intuitive, after all this clause of the command always comes first of all, the WHERE is executed always before, so he chooses which rows will be captured in this query and then he will see which columns will be used to produce the final result. But that alone is not a technical impediment. It turns out that there are cases that this the SELECT is executed to produce a result that makes sense for the alias exist. Then you end up getting into a problem of the egg and the chicken, to make the WHERE, need to execute the SELECT and to execute the SELECT need to execute the WHERE.

For some reason they thought HAVING should perform after the SELECT. Obviously he must be after the WHERE, I think this has no doubt, but it could be before the SELECT. This is not part of the pattern and as far as I know only Mysql allows. They found it useful and worth the effort, and it would not have the same problem. There is no situation that one depends on the other to happen. At least they thought that.

In the example the count only occurs after all the WHERE rotate. Already the HAVING occurs at the end of the whole WHERE, and whether the WHERE finished it can run the SELECT. Having the SELECT has the alias, then you can use in the HAVING.

Did you stop to think that he is giving you the same result, but maybe not so efficient? I do not say in this case, but in some this can happen, the WHERE is different from HAVING (you are using a mechanism to analyze clusters as line analysis), even giving the same result it has a different commitment. See if it took the same and send explain the query to see if the effort is the same.

If you ask if I should let you use the alias in the WHERE where there is no dependency, I would say that if the analyzer understands that there is no problem, should let use.

I don’t think they tried too hard because we believe alias simple that it does not have to cause problem is only a convenience not very important and has there its negative point also for the readability (as well as has positive for the same aspect).

Normal running order (Mysql changes slightly):

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY

SQL Server Documentation (has more clauses).

  • In fact the use of HAVING this way is possible only in Mysql. However I had no idea of the order of application of the clauses, and that this would impact the final result. I believe that the essential in cases like this would be to use a subquery, since as you mentioned the HAVING focuses on cluster analysis. Excellent response, I wanted to know precisely how this process took place.

Browser other questions tagged mysql sql where

You are not signed in. Login or sign up in order to post.