SQL WHERE clause with computed value

Question

It seems like I can't use the calculated value in a sentence WHERE?

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

I get it ERROR 1054 (42S22): Unknown column 'age' in 'where clause'. A possible workaround is to use a subquery, but does this complicate the situation too much?

Answer 1

You can use the calculated value in a sentence WHERE, but not its alias. You need to enter the whole expression again

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

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

SQL WHERE. , , WHERE, .

Answer 2

, mySQL ,

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

mySQL WHERE CLAUSE , .

Answer 3

, WHERE. HAVING. ORDER BY ( ). .

Answer 4

sql , T-SQL SQL-,

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

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

WHERE , . WHERE INDEX dateOfBirth.

, , SQL Server WHERE,

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

INDEX dateOfBirth,