• expr  BETWEEN min  AND max 

If expr  is greater than or equal to min  and expr  is less than or equal to max , BETWEEN  returns 1 , otherwise it returns 0 . This is equivalent to the expression ( min  <= expr  AND expr  <= max )  if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation” , but applied to all the three arguments. 

mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0 mysql> SELECT 1 BETWEEN 2 AND 3; -> 0 mysql> SELECT ’b’ BETWEEN ’a’ AND ’c’; -> 1 mysql> SELECT 2 BETWEEN 2 AND ’3’; -> 1 mysql> SELECT 2 BETWEEN 2 AND ’x-3’; -> 0 

For best results when using BETWEEN  with date or time values, use CAST()  to explicitly convert the values to the desired data type. Examples: If you compare a DATETIME  to two DATE  values, convert the DATE  values to DATETIME  values. If you use a string constant such as ’2001-1-1’  in a comparison to a DATE , cast the string to a DATE . 

• expr  NOT BETWEEN min  AND max 

This is the same as NOT ( expr  BETWEEN min  AND max ) . 

• COALESCE( value ,...) 

Returns the first non- NULL  value in the list, or NULL  if there are no non- NULL  values. 

The return type of COALESCE()  is the aggregated type of the argument types. 

mysql> SELECT COALESCE(NULL,1); -> 1 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL 

• GREATEST( value1 , value2 ,...) 

With two or more arguments, returns the largest (maximum-valued) argument. The arguments are compared using the same rules as for LEAST() . 

mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0 mysql> SELECT GREATEST(’B’,’A’,’C’); -> ’C’ 

GREATEST()  returns NULL  if any argument is NULL . 

• expr  IN ( value ,...) 

Returns 1  (true) if expr  is equal to any of the values in the IN()  list, else returns 0  (false). 

Type conversion takes place according to the rules described in Section 12.3, “Type Conversion in Expression Evaluation” , applied to all the arguments. If no type conversion is needed for the values in the IN()  list, they are all non- JSON  constants of the same type, and expr  can be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The 

