Using a variable in MySQL Select "Statute" in the "Where" section

Question

I would like to know if it is possible to find the value in the select statement and use it in the where clause, for example:

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

the “table” is quite large, and I want to narrow down the records that the query should look at as quickly as possible.

I know that this is not a bit of code, it is impossible, as it is written (# 1054 - Unknown column "variable" in the "where" section), but still you need to find out the value of "variable" and then use this in the WHERE clause?

Answer 1

You can try the 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 more details.

Answer 2

In some cases, you can replicate an expression in a WHERE clause, as shown by RedFilter. In other cases, this is not possible, and you can use the HAVING clause, for example

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

HAVING is less efficient, think of it as a post-processor of results, and not that the query optimizer can do anything.