Using a variable in MySQL Select Statment in a Where Clause

Question

I would like to know if it's possible to find a value in a select statement and use it in a where clause like:

SELECT col1, MAX(col2) - COUNT(DISTINCT col3) as variable
FROM table
WHERE col1 > variable

"table" is pretty big, and I want to narrow down the records the query has to look at as quickly as possible.

I know this isn't bit of code isn't possible as written (#1054 - Unknown column 'variable' in 'where clause'), but is there anyway to figure out the value of "variable" and then use it in the WHERE clause?

Answer 1

In some cases you can replicate an expression in a WHERE clause, as demonstrated by RedFilter. In other cases, this isn't possible and you can use a HAVING clause, e.g.

SELECT col1, MAX(col2) - COUNT(DISTINCT col3) as variable
FROM table
HAVING col1 > variable

HAVING is less efficient, think of it as a resultset post-processor rather than something the query optimizer can do anything with.

Answer 2

You could try subquery syntax, also called nested select.

I think something like:

SELECT col1 WHERE col1 > (SELECT MAX(col2) - COUNT(DISTINCT col3)) 

See the MySQL manual for some better examples.