Search code examples
mysqlvariableswhere-clausetemporary

can MySQL temporary variables be used in WHERE clause?

In MySQL, can temporary variables be used in the WHERE clause?

For example, in the following query:

SELECT `id`, @var := `id` * 2 FROM `user`

@var is successfully set to twice the value of `id`

However, if I try to filter the result set to only include results where @var is less than 10:

SELECT `id`, @var := `id` * 2 FROM `user` WHERE @var < 10

then I get no results.

How can I filter the results based on the value of @var?

Solution

  • You need to assign an alias, and test it in the HAVING clause:

    SELECT id, @var := id * 2 AS id_times_2
FROM user
HAVING id_times_2 < 10

    Note that if you're just using the formula for filtering, not for communicating internal results from one row to the next, you don't need the variable at all. You can write:

    SELECT id, id * 2 AS id_times_2
FROM user
HAVING id_times_2 < 10