SQL WHERE clause with computed value

Question

It appears that I cannot use a computed value in a WHERE clause?

SELECT id, TIMESTAMPDIFF(YEAR,dateOfBirth,CURDATE()) AS age 
FROM user
WHERE age >= 20

I get ERROR 1054 (42S22): Unknown column 'age' in 'where clause'. A possible workaround is to use subquery but it overly complicate things?

Answer 1

You can use a computed value in a WHERE clause but not its Alias. You have to type the whole expression again

WHERE TIMESTAMPDIFF(YEAR,dateOfBirth,CURDATE()) >=20

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

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.

Reference

Answer 2

Yes, but mySQL behaviour isn't like you think

SELECT id, TIMESTAMPDIFF(YEAR,dateOfBirth,CURDATE()) AS age 
FROM user
WHERE TIMESTAMPDIFF(YEAR,dateOfBirth,CURDATE()) >= 20

mySQL WHERE CLAUSE only look into columns and not into alias.