Compare result of 'as' expression in 'where' statement.

Question

Is it possible to create a MySQL select statement that uses an expression as x then checks if the value for x is under a certain amount?

SELECT (mytable.field1 + 10) AS x FROM `mytable` WHERE x < 50;

Answer 1

no you have to actually do this

SELECT (mytable.field1 + 10) AS x FROM `mytable` WHERE (mytable.field1 + 10) < 50;

Answer 2

You could also do it like this:

SELECT *
FROM (
    SELECT (mytable.field1 + 10) AS X
    FROM `MyTable`
) t
WHERE X < 50

In most cases it's probably better to use Nick's solution, since it allows the server to apply the where clause earlier in the process. However, occasionally you will want to do it this way for the sake of clarity in complex queries where other factors are driving the performance.

Answer 3

I store expressions in variables and interpolate them in multiple places for this:

$x_sql = '(mytable.field1 + 10)';
$SQL = "SELECT $x_sql AS x FROM mytable WHERE $x_sql < 50";

Or if you aren't bothered by the inefficiency, use a HAVING clause:

SELECT (mytable.field1 + 10) as x FROM mytable HAVING x < 50;

(probably equally inefficient to the subselect suggested in another answer).