Compare the result of the expression 'as' in the expression 'where'

Question

Is it possible to create a MySQL select statement that uses an expression like x and checks if the value for x is to a certain extent?

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

Answer 1

no you really need to do this

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

Answer 2

You can also do it like this:

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

In most cases, it is probably best to use Nick's solution, as it allows the server to apply the where clause earlier in the process. However, sometimes you want to do this in such a way that clarity in complex queries is when other factors affect performance.

Answer 3

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

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

Or, if you're not worried about inefficiency, use the HAVING clause:

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

(probably just as ineffective for the subtitle suggested in another answer).