Use aliases in a query condition syntax?

Question

I'm wondering if is possible use a aliases in a where condition.

For exemple this:

SELECT name AS NAMESS, age from user AS MEN where MEN.name LIKE '%Cortan%';

Trying to look like this:

SELECT name AS NAMESS, age from user AS MEN where MEN.NAMESS LIKE '%Cortan%';

What is the correct syntax?

No. The "Where" statement is evaluated before select statement and the aliases. — WorkSmarter, May 25 '15 at 23:31
SO post with a solution using a temp table http://stackoverflow.com/questions/9664657/column-alias-in-a-where-clause — WorkSmarter, May 25 '15 at 23:34

Gordon Linoff · Accepted Answer · 2015-05-26T13:41:47.727

(The cited duplicate was not quite a duplicate, because it is not specific for MySQL.)

The answer to your question is that you can never use:

SELECT name AS NAMESS, age
from user AS MEN
where MEN.NAMESS LIKE '%Cortan%';

unless NAMESS is a column in user. You could do a subquery:

SELECT NAMESS, age
from (SELECT u.*, u.name as NAMESS FROM user u) MEN
where MEN.NAMESS LIKE '%Cortan%';

And this would work.

You can remove the MEN. qualification and use a HAVING clause (this is a MySQL extension):

SELECT name AS NAMESS, age
FROM user MEN
HAVING NAMESS LIKE '%Cortan%';

Or, you can do what countless others do, and simply refer to the column/expression before the alias:

SELECT name AS NAMESS, age
FROM user MEN
WHERE MEN.names LIKE '%Cortan%';

The MEN. is optional, but if you have multiple tables, qualified column names are highly recommended.

1 Answers1