Search code examples
mysqlsqlmysql-error-1054

Why Can't I Use Defined Variable in Where Clause

My query is:

SELECT 
  offer, 
  (SELECT 
        AVG(offer) 
   FROM 
        project_bids
  ) as var1 
FROM 
  `project_bids` 
WHERE 
  offer > var1

It causes "#1054 - Unknown column 'var1' in 'where clause'" error. Can anybody expalain why gives that error ? (i know working sql but i want to learn why it fails)

Solution

  • The sequence of execution of clauses of a SELECT statement is mentioned here:

    http://blog.sqlauthority.com/2007/06/14/sql-server-easy-sequence-of-select-from-join-where-group-by-having-order-by/

    Alias of an column can not be used in any clause except the last clause "ORDER BY".