5

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?

Leo Galleguillos
  • 2,429
  • 3
  • 25
  • 43
  • 1
    `SELECT id, @var := id * 2 FROM user WHERE id < 5` ? (Sorry, couldn't resist...) – Pixelchemist Jul 07 '14 at 17:31
  • Anything declared in the `SELECT` clause can only be referenced in `HAVING`. `WHERE` can only access table data. – Barmar Jul 07 '14 at 17:31
  • @Pixelchemist Haha, thank you. However, I'm looking for a way to filter on temporary variables, not table columns. In our actual queries, the value of @var is based on more complicated logic, and it would be redundant to repeat the logic in both the `SELECT` and `WHERE` clauses. – Leo Galleguillos Jul 07 '14 at 17:37
  • @Barmar The following query does not work either: `SELECT \`id\`, @var := \`id\` * 2 FROM \`user\` HAVING @var < 10` – Leo Galleguillos Jul 07 '14 at 17:38

2 Answers2

15

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
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Strictly speaking, the predicate in the HAVING clause is _not_ **"based on the value of @var"**. Note that the assignment to the user defined variable **`@var :=`** could be removed from the statement, and the returned result would be exactly the same. (The difference would be the assignment of a value to the user-defined variable, which would be determined from the last row processed.) – spencer7593 Jul 07 '14 at 18:31
  • Good point, I've added a query that doesn't use the variable. User variables are generally only needed to pass values between rows, such as running totals, row counters, etc. – Barmar Jul 07 '14 at 20:06
3

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.

spencer7593
  • 106,611
  • 15
  • 112
  • 140