Column Alias in a WHERE Clause

Question

Problem

I am using alternate column name (alias) in a Query, I can use the alias "given_name" as part of the ORDER BY but am unable to use it as part of the WHERE clause. The WHERE "given_name" is passed in as the result of a request out of my control and I do not know the actual column name that should be used in the WHERE condition.

Question

1. It there a way/hack to use a column alias in a WHERE clause?
2. Is there a way to find the column name from an alias?

Research

After some research it looks like alias are added after after the WHERE clause.

Example

SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name'
FROM green_profile profile 
LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 
LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1 
WHERE given_name LIKE 'levi%' 
ORDER BY given_name DESC LIMIT 0 , 25

Clarettaclarette

Answer 1

Untested, but this hack should work...

SELECT * FROM (  
    SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name'
    FROM green_profile profile 
    LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 
    LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1   
) as temptable
WHERE given_name LIKE 'levi%' 
ORDER BY given_name DESC LIMIT 0 , 25

It works by simply creating a temporary table from your original select statement (without the where clause and ordering), which has the column names you specify. You then select from this with the column names you want.

A better approach might be to create a view, with the column names you want, and select from the view...

CREATE VIEW newtable AS
SELECT profile.id AS id, given.name AS 'given_name', family.name AS 'family_name'
FROM green_profile profile 
LEFT JOIN green_name given ON given.profileid = profile.id AND given.name_typeid = 0 
LEFT JOIN green_name family ON family.profileid = profile.id AND family.name_typeid = 1;

And then...

SELECT * FROM newtable
WHERE given_name LIKE 'levi%' 
ORDER BY given_name DESC LIMIT 0 , 25

Eiten

Thanks, great explanation. Both solutions work however after benchmarking against a large table the view solution provides much better performance. – Clarettaclarette

I suspected that might be so, as it will only have to do the full select statement once for the view, then can cache and index it for future calls (not sure how the details of this works). I expect the performance will be slightly better just referring to the original column names, and not dealing with the view all-together, but hopefully not such a big difference. – Eiten

Answer 2

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.

Purr

Answer 3

In in doubt, simply refer to the column by number:

...
ORDER BY 2
...

Venezuela

Won't it be better to use the full name given.name? column order isn't really a robust approach – Zarah

This works however the "given_name" was passed in as the result of client request out of my control. I have no way of determining the column number or that "given_name" is an alias for given.name. – Clarettaclarette