12.3.2 Comparison Functions and Operators

Table 12.3 Comparison Operators

BETWEEN ... AND ... Check whether a value is within a range of values
COALESCE() Return the first non-NULL argument
<=> NULL-safe equal to operator
= Equal operator
>= Greater than or equal operator
> Greater than 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 NOT Test a value against a boolean
IS NULL NULL value test
IS Test a value against a boolean
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
<= Less than or equal operator
< Less than 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, “Row Subqueries”.

Some of the functions in this section (such as LEAST() and GREATEST()) return values other than 1 (TRUE), 0 (FALSE), or NULL. However, the value they return is based on comparison operations performed according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”.

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 latin1 (cp1252 West European), which also works well for English.