Did you know that if you define an alias when selecting columns, you probably can’t use it inside of a WHERE clause without getting an Unknown Column error?
The standard explanation given by This site (http://dev.mysql.com/doc/refman/5.0/en/problems-with-alias.html) and used by many of the sites that I found with google explains it by saying that:
Standard SQL disallows references to column aliases in a
WHEREclause. This restriction is imposed because when the
WHEREclause is evaluated, the column value may not yet have been determined.
I think I found a layman’s explanation for the problem here:
SQL is evaluated backwards, from right to left. So the where clause is parsed and evaluate prior to the select clause. Because of this the aliasing of u_name to user_name as not yet occurred.
And I believe a workable solution can be found here:
So…instead of using WHERE, use HAVING.
Here’s the conclusion of when to use either as found by:
Here’s the rule. If a condition refers to an aggregate function, put that condition in the HAVING clause. Otherwise, use the WHERE clause.
Here’s another rule: You can’t use HAVING unless you also use GROUP BY.