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 🙂

Advertisements

Let me know what you think .. :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s