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

Brad Johnson. asked, May 10 ' 2016

error :

 #1054 - Unknown column 'start' in 'where clause'

 

Query : 

SELECT SUBSTRING_INDEX(`age_in_day`, '-', 1) as start, SUBSTRING_INDEX(`age_in_day`, '-', -1) FROM `volume_limit` where start <= 32 and end >= 32;

 

  • MySQL
  • where-clause
  • alias

1 Answers

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 WHERE vs HAVING though.

Leave a Ansewer