sqlmysql

Reusing a calculated column in where clauses

Since you can't use a calculated column in a where-clause in MySQL like this:

SELECT a, b, c, (a * b + c) AS d FROM table
WHERE d > n
ORDER by d

you have to use

SELECT a, b, c, (a * b + c) AS d FROM table
WHERE (a * b + c) > n
ORDER by d

I'm not sure if the calculation is executed once per row or twice in this case. But even just to avoid cluttering up the query I'm wondering if there a way to avoid repeating the calculation. I find it strange it's possible to ORDER on the calculated result but not to use it in a WHERE clause.

Solution

  • You can use HAVING to filter on a computed column:

    SELECT a,b,c,(a*b+c) AS d, n FROM table
HAVING d > n
ORDER by d

    Note that you need to include n in the SELECT clause for this to work.