4

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?

Jiew Meng
  • 84,767
  • 185
  • 495
  • 805
  • Read about **[`LOGICAL QUERY PROCESSING`](http://stackoverflow.com/questions/32668427/why-do-linq-to-sql-queries-starts-with-the-from-keyword-unlike-regular-sql-que/32668470#32668470)** and it will be clear why you can't use aliases in `WHERE` – Lukasz Szozda Sep 22 '15 at 04:47

4 Answers4

7

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

Hanky Panky
  • 46,730
  • 8
  • 72
  • 95
3

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.

Mike
  • 1,231
  • 12
  • 17
0

As others have pointed out, you cannot use a column alias in a WHERE clause. Same is true for a HAVING clause. But you can use a column alias in a ORDER BY clause (as long as it is not an expression to be evaluated). Hence the confusion.

Emacs User
  • 1,457
  • 1
  • 12
  • 19
0

I could prefer to execute the above sql in the following way Please note that I changed the query and re-code it in T-SQL But I believe the logic of the SQL engines that will execute it the same

declare @curdate datetime = getdate()
declare @date20 datetime = dateadd(yy,-20,@curdate)

SELECT id, DATEDIFF(YEAR,dateOfBirth,@curdate) AS age 
FROM [user]
WHERE dateOfBirth <= @date20

In WHERE clause I preferred to use a static variable whose value is calculated before. This type of WHERE criteria enables the use of INDEX on in our case dateOfBirth column. And this boosts the performance of the query

Otherwise, at least in SQL Server a WHERE clause like below

WHERE DATEDIFF(YEAR,dateOfBirth,@curdate) >= 20

will fail the use of INDEX on dateOfBirth column and so the query performance

Eralper
  • 6,461
  • 2
  • 21
  • 27