MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

12.3.2 Comparison Functions and Operators

Table 12.3 Comparison Operators

Name Description
BETWEEN ... AND ... Check whether a value is within a range of values
COALESCE() Return the first non-NULL argument
= Equal operator
<=> NULL-safe equal to operator
> Greater than operator
>= Greater than or equal operator
GREATEST() Return the largest argument
IN() Check whether a value is within a set of values
INTERVAL() Return the index of the argument that is less than the first argument
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
< Less than operator
<= Less than or equal operator
LIKE Simple pattern matching
NOT BETWEEN ... AND ... Check whether a value is not within a range of values
!=, <> Not equal operator
NOT IN() Check whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings

Comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL. These operations work for both numbers and strings. Strings are automatically converted to numbers and numbers to strings as necessary.

The following relational comparison operators can be used to compare not only scalar operands, but row operands:

=  >  <  >=  <=  <>  !=

The descriptions for those operators later in this section detail how they work with row operands. For additional examples of row comparisons in the context of row subqueries, see Section 13.2.11.5, “Row Subqueries”.

Some of the functions in this section return values other than 1 (TRUE), 0 (FALSE), or NULL. LEAST() and GREATEST() are examples of such functions; Section 12.2, “Type Conversion in Expression Evaluation”, describes the rules for comparison operations performed by these and similar functions for determining their return values.

Note

In previous versions of MySQL, when evaluating an expression containing LEAST() or GREATEST(), the server attempted to guess the context in which the function was used, and to coerce the function's arguments to the data type of the expression as a whole. For example, the arguments to LEAST("11", "45", "2") are evaluated and sorted as strings, so that this expression returns "11". In MySQL 8.0.3 and earlier, when evaluating the expression LEAST("11", "45", "2") + 0, the server converted the arguments to integers (anticipating the addition of integer 0 to the result) before sorting them, thus returning 2.

Beginning with MySQL 8.0.4, the server no longer attempts to infer context in this fashion. Instead, the function is executed using the arguments as provided, performing data type conversions to one or more of the arguments if and only if they are not all of the same type. Any type coercion mandated by an expression that makes use of the return value is now performed following function execution. This means that, in MySQl 8.0.4 and later, LEAST("11", "45", "2") + 0 evaluates to "11" + 0 and thus to integer 11. (Bug #83895, Bug #25123839)

To convert a value to a specific type for comparison purposes, you can use the CAST() function. String values can be converted to a different character set using CONVERT(). See Section 12.10, “Cast Functions and Operators”.

By default, string comparisons are not case-sensitive and use the current character set. The default is utf8mb4.