Why variables cannot be used in where clause of mysql

Question

Using Mysql this statement works:

SELECT *, ABS(UNIX_TIMESTAMP(time) - 
       UNIX_TIMESTAMP('2013-11-14 14:35:21') ) as diff from Alert
order by diff
limit 3

But if I add a where clause as follows, it gives "Unknown column 'diff' in 'where clause'" error

SELECT *, ABS(UNIX_TIMESTAMP(time) - 
       UNIX_TIMESTAMP('2013-11-14 14:35:21') ) as diff from Alert
where diff < 2
order by diff
limit 3

Ok I change the statement to using the Having clause as follows:

SELECT *, ABS(UNIX_TIMESTAMP(time) - 
       UNIX_TIMESTAMP('2013-11-14 14:35:21') ) as diff from Alert
order by diff
having diff<2

It is still giving me You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'having diff<2' at line 4.

Answer 1

Use

SELECT *, ABS(UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP('2013-11-14 14:35:21') ) as diff 
from Alert 
order by 
    ABS(UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP('2013-11-14 14:35:21') ) 
limit 3

You can't use the named column computation in the order by (or where) clause.

Answer 2

You can not use the alias name in the where clause, but with order by it works because order by happens after the full result is fetched by the query.

Your third query is giving error because i think the correct syntax is first having then order by

SELECT *, ABS(UNIX_TIMESTAMP(time) - UNIX_TIMESTAMP('2013-11-14 14:35:21') ) as diff 
from Alert having diff<2 order by diff 

Some more details

WHERE is used while listing and no ALIAS names are available yet

HAVING filters rows after listing all possible rows so ALIAS names are generated