MIN() in WHERE clause

Question

I need to write a simple query in mySQL that would use MIN() in WHERE clause, calculating a minimum value of two numbers, where one of them is column value (with possible NULL value) and other one is a fixed number, something like this >

SELECT ... WHERE ... (len <= MIN(maxStay, 365))

I know I can write something like >

SELECT ... WHERE ... ((len <= maxStay) OR (maxStay IS NULL)) AND (len <= 365)

but I would prefer something simple, such as using MIN() in WHERE clause. Is it possible? And if so, which has better performance?

Answer 1

min and max are aggregate functions and cannot be used in the where clause, although they can be used in the having clause.

But I think you are looking for LEAST, although it doesn't work with NULL values. If you write LEAST(1, NULL), it will always return NULL, whichever value you put instead of 1.

You could use IFNULL to work around that:

SELECT 
  ...
WHERE
  len <= LEAST(IFNULL(maxStay, 365), 365)

Answer 2

To return the lesser of the two values, use the LEAST function in MySql. To handle the null values, use IFNULL().

WHERE len <= LEAST(IFNULL(maxStay, 365), 365)