MySQL - SELECT AS in WHERE

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.

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,

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'

val is not defined, it's just an alias. Do it like this:

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

Related