Using column alias in WHERE clause of MySQL query produces an error

Question

The query I m running is as follows  however I m getting this error       1054 - Unknown column  guaranteed postcode  in  IN ALL ANY subquery    SELECT  users   first name    users   last name    users   email   SUBSTRING  locations   raw  -6 4  AS  guaranteed postcode  FROM  users  LEFT OUTER JOIN  locations  ON  users   id     locations   user id  WHERE  guaranteed postcode  NOT IN  this is where the fake col is being used    SELECT  postcode  FROM  postcodes  WHERE  region  IN       australia         My question is  why am I unable to use a fake column in the where clause of the same DB query

User · Answer

You can use HAVING clause for filter calculated in SELECT fields and aliases

User · Answer

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   Copied from MySQL documentation As pointed in the comments  using HAVING instead may do the work  Make sure to give a read at this question too  WHERE vs HAVING

User · Answer

As Victor pointed out  the problem is with the alias  This can be avoided though  by putting the expression directly into the WHERE x IN y clause   SELECT  users   first name   users   last name   users   email  SUBSTRING  locations   raw  -6 4  AS  guaranteed postcode  FROM  users  LEFT OUTER JOIN  locations  ON  users   id     locations   user id  WHERE SUBSTRING  locations   raw  -6 4  NOT IN  this is where the fake col is being used    SELECT  postcode  FROM  postcodes  WHERE  region  IN       australia         However  I guess this is very inefficient  since the subquery has to be executed for every row of the outer query

User · Answer

You can use SUBSTRING locations raw -6 4   for where conditon  SELECT  users   first name    users   last name    users   email   SUBSTRING  locations   raw  -6 4  AS  guaranteed postcode  FROM  users  LEFT OUTER JOIN  locations  ON  users   id     locations   user id  WHERE SUBSTRING  locations   raw  -6 4  NOT IN  this is where the fake col is being used   SELECT  postcode  FROM  postcodes  WHERE  region  IN     australia

User · Answer

Standard SQL disallows references to column aliases in a WHERE clause  This restriction is imposed because when the WHERE clause is evaluated  the column value may not yet have been determined  For example  the following query is illegal   SELECT id  COUNT    AS cnt FROM tbl name   WHERE cnt   0 GROUP BY id

User · Answer

Standard SQL  or MySQL  does not permit the use of column aliases in a WHERE clause because      when the WHERE clause is evaluated  the column value may not yet have been determined     from MySQL documentation   What you can do is calculate the column value in the WHERE clause  save the value in a variable  and use it in the field list  For example you could do this   SELECT  users   first name    users   last name    users   email    postcode AS  guaranteed postcode  FROM  users  LEFT OUTER JOIN  locations  ON  users   id     locations   user id  WHERE   postcode    SUBSTRING  locations   raw  -6 4   NOT IN    SELECT  postcode  FROM  postcodes  WHERE  region  IN       australia         This avoids repeating the expression when it grows complicated  making the code easier to maintain

User · Answer

Maybe my answer is too late but this can help others    You can enclose it with another select statement and use where clause to it    SELECT   FROM  Select col1  col2      as t WHERE t calcAlias  gt  0   calcAlias is the alias column that was calculated

User · Answer

I am using mysql 5 5 24 and the following code works     select   from   SELECT  users   first name    users   last name    users   email   SUBSTRING  locations   raw  -6 4  AS  guaranteed postcode  FROM  users  LEFT OUTER JOIN  locations  ON  users   id     locations   user id    as a WHERE guaranteed postcode NOT IN --this is where the fake col is being used    SELECT  postcode  FROM  postcodes  WHERE  region  IN       australia

[mysql] Using column alias in WHERE clause of MySQL query produces an error

Examples related to mysql

Examples related to sql

Examples related to mysql-error-1054