Daily Archives: September 13, 2011

Use of Aliases in mysql where clause

Wanted to use an alias in a select statement’s where clause in one my mysql databases. But I continued to get errors when I used a code like this,

select fname firstname, lname lastname from tbl_student where firstname = ‘suren’;

So I did a little bit of digging. I have used the above style in oracle but for some reason in mysql it was giving and error. Then in mysql documentation I found that in mysql the where clause get executed first and then only the select clause aliases are created. So by the time the where clause is considered the aliases have not yet been created. That is what gives the error.

Solution I came up with is to use Having clause… there are no problems in using a alias in the having clause as it gets executed last in the query. Not sure how much this is costing on performance wise but it is a solution. Let me know if you find a better solution.

so the correct form for above query would be,

select fname firstname, lname lastname from tbl_student having firstname = ‘suren”;

Hope this helps and let me know if there is a better way to do this. thanks and have fun 🙂