MySQL - SELECT AS in WHERE

Question

For some reason, this doesn't work:

select substring(rating, instr(rating,',') +1, +2) as val from users where val = '15'; 

It gives this error:

ERROR 1054 (42S22): Unknown column 'val' in 'where clause'

How do I do it then?

Answer 1

First, you cannot use ALIAS on the WHERE clause. You shoul be using the column,

SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val  FROM   users  WHERE  SUBSTRING(rating, INSTR(rating,',') +1, +2) = '15' 

The reason is as follows: the order of operation is SQL,

• FROM clause
• WHERE clause
• GROUP BY clause
• HAVING clause
• SELECT clause
• ORDER BY clause

the ALIAS takes place on the SELECT clause which is before the WHERE clause.

if you really want to use the alias, wrap it in a subquery,

SELECT * FROM     (         SELECT SUBSTRING(rating, INSTR(rating,',') +1, +2) AS val          FROM   users     ) s WHERE   val  = '15' 

Answer 2

You sure can with MySQL, please see below, just tested this and it works fine:

Select substring(rating, instr(rating,',') +1, +2) as val  From users  Having val = '15'; 

You can also do things like "having val is null" etc.

With ALIASes you can't use WHERE, but using HAVING does the trick.

H