can MySQL temporary variables be used in WHERE clause?

Question

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?

Answer 1

Q: Can MySQL temporary variables be used in WHERE clause?

Yes. MySQL user-defined variables (e.g. @var) can be referenced in a WHERE clause.

The user-defined variable is a reference to whatever value is currently assigned to it at the time the expression is evaluated.

In your query, the predicates in the WHERE clause are evaluated before the expressions in the SELECT list.

That is, @var < 10 is evaluated as a boolean expression while rows are accessed; for each candidate row, the expression is evaluated, and the row is returned only if the result is TRUE.

If you were to provide a value for that variable that had a numeric value greater than or equal to ten, before the statement was executed, then ALL of the rows would be returned.

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

You can't really. (Actually, that's what your original query is doing.)

You can include predicates on expressions other than @var; and those expressions can be derived from the value assigned to @var.

As one option, you could return an expression in the SELECT list, and then use a HAVING clause to filter the rows returned. (NOTE: the HAVING clause is evaluated after the resultset is prepared; unlike the WHERE clause, which is evaluated at the time rows are accessed.)

Another alternative is to use an inline view to prepare the resultset, and then an outer query can use a WHERE clause on an expression that is returned.

But strictly speaking, those those approaches are both applying predicates on expressions other than @var; they aren't actually performing a test of the value in @var.

Answer 2

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