sql - 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'