MYSQL: how to use virtual field in where using left join query?

Question

I have to find user using age. But, age field not available in my user_basic_info table. I have one dob field in user_basic_info table. i have create virtual age field. but, i have return one error "Unknown column 'age' in 'where clause'"

My Query:

SELECT
    `users`.`nickname`,
    TIMESTAMPDIFF(YEAR,user_basic_info.dob,CURDATE()) AS `age`
FROM `users`
LEFT JOIN `user_basic_info`
    ON user_basic_info.user_id = users.id
WHERE
    (`users`.`status`=1) AND
    ((age >= 22) OR (age <= 30))

You can utilize alias of column in where clause because of sql execution behavior. Please answer here https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order. This is why you can utilize alias in group by , having and order by clause. — Fahad Anjum, Oct 06 '17 at 12:14

Tim Biegeleisen · Answer 1 · 2017-10-06T12:40:12.233

You are getting this error because the age alias is not available in the WHERE clause at the time when it is evaluated. Your options include either repeating the expression for age in the WHERE clause (ugly) or doing a subquery to use the alias (potentially not performant). For the purpose of readability I might do this:

SELECT
    nickname,
    age
FROM
(
    SELECT
        users.nickname,
        users.status,
        TIMESTAMPDIFF(YEAR, user_basic_info.dob, CURDATE()) AS age
    FROM users
    LEFT JOIN user_basic_info
        ON user_basic_info.user_id = users.id
) t
WHERE
    status = 1 AND age BETWEEN 22 AND 30;

If you are performance-minded, then you can just repeat the expression for age in the WHERE clause of your original query:

SELECT
    users.nickname,
    TIMESTAMPDIFF(YEAR,user_basic_info.dob,CURDATE()) AS age
FROM users
LEFT JOIN user_basic_info
    ON user_basic_info.user_id = users.id
WHERE
    user.status = 1 AND
    TIMESTAMPDIFF(YEAR, user_basic_info.dob, CURDATE()) BETWEEN 22 AND 30;

score 1 · Answer 2 · answered Oct 06 '17 at 12:11

1

The second Where clause that references age should be moving to a HAVING clause:

SELECT
    u.`nickname`,
    TIMESTAMPDIFF(YEAR,user_basic_info.dob,CURDATE()) AS age
FROM `users` u
LEFT JOIN `user_basic_info`
    ON user_basic_info.user_id = u.id
WHERE
    (u.`status`=1)
HAVING
    ((age >= 22 && age <= 30))

Note that I've edited the Having to work more as a 'between', otherwise they don't really do much.

(i.e. a 13 year old isn't older than 22, but is younger than 30, so is included. A 99 year old is older than 22 so is also included)

As a result, making them an AND will retrieve anyone between 22 and 30 inclusive.

answered Oct 06 '17 at 12:11

Chris J

1,441
9
19

"What's the point of a having clause when an and would do?" Because the `where` clause filters the resultset before a `having` is applied, so there is a performance difference. – Chris J Oct 06 '17 at 12:33
I don't think it's about performance more likely order of execution having comes after select whereas where comes before it so age is visible to having but not to where. – P.Salmon Oct 06 '17 at 12:40
Upvoted for mentioning an option I missed: using the overloaded `HAVING` clause which _can_ use aliases from the `SELECT` clause. – Tim Biegeleisen Oct 06 '17 at 12:42
"Have you tested this? I thought 'if the GROUP BY clause is omitted, the HAVING clause behaves like the WHERE' so I figured the same issue as using where clause to test age would come into play." Have you tested it? You can't use field names in a `HAVING` clause, it will only accept aliases. – Chris J Oct 06 '17 at 12:42

Puja · Answer 3 · 2017-10-06T12:16:25.583

You can't use age alias directly in WHERE condition for that you have used HAVING clause with your condition.

SELECT
    `users`.`nickname`,
    TIMESTAMPDIFF(YEAR,user_basic_info.dob,CURDATE()) AS `age`
FROM `users`
LEFT JOIN `user_basic_info`
    ON user_basic_info.user_id = users.id
WHERE
    (`users`.`status`=1)
  HAVING  ((TIMESTAMPDIFF(YEAR,user_basic_info.dob,CURDATE())>= 22) OR (TIMESTAMPDIFF(YEAR,user_basic_info.dob,CURDATE())<= 30))

score 0 · Answer 4 · answered Oct 06 '17 at 12:28

You should try to use Having with age as follows:

SELECT u.nickname,
TIMESTAMPDIFF(YEAR,ubi.dob,CURDATE()) AS age
FROM users as u
LEFT JOIN user_basic_info as ubi 
ON ubi.user_basic_info.user_id = u.users.id
WHERE u.status = 1 
HAVING
u.age >= 22 OR u.age <= 30

MYSQL: how to use virtual field in where using left join query?

4 Answers4