MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0

**Table 12.3 Comparison Operators**

Name | Description |
---|---|

`BETWEEN ... AND ...` |
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()` |
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 ...` |
Whether a value is not within a range of values |

`!=` , `<>` |
Not equal operator |

`NOT IN()` |
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`

.

Equal:

mysql>

-> 0 mysql>`SELECT 1 = 0;`

-> 1 mysql>`SELECT '0' = 0;`

-> 1 mysql>`SELECT '0.0' = 0;`

-> 0 mysql>`SELECT '0.01' = 0;`

-> 1`SELECT '.01' = 0.01;`

For row comparisons,

`(a, b) = (x, y)`

is equivalent to:(a = x) AND (b = y)

`NULL`

-safe equal. This operator performs an equality comparison like the`=`

operator, but returns`1`

rather than`NULL`

if both operands are`NULL`

, and`0`

rather than`NULL`

if one operand is`NULL`

.The

`<=>`

operator is equivalent to the standard SQL`IS NOT DISTINCT FROM`

operator.mysql>

-> 1, 1, 0 mysql>`SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;`

-> 1, NULL, NULL`SELECT 1 = 1, NULL = NULL, 1 = NULL;`

For row comparisons,

`(a, b) <=> (x, y)`

is equivalent to:(a <=> x) AND (b <=> y)

Not equal:

mysql>

-> 1 mysql>`SELECT '.01' <> '0.01';`

-> 0 mysql>`SELECT .01 <> '0.01';`

-> 1`SELECT 'zapp' <> 'zappp';`

For row comparisons,

`(a, b) <> (x, y)`

and`(a, b) != (x, y)`

are equivalent to:(a <> x) OR (b <> y)

Less than or equal:

mysql>

-> 1`SELECT 0.1 <= 2;`

For row comparisons,

`(a, b) <= (x, y)`

is equivalent to:(a < x) OR ((a = x) AND (b <= y))

Less than:

mysql>

-> 0`SELECT 2 < 2;`

For row comparisons,

`(a, b) < (x, y)`

is equivalent to:(a < x) OR ((a = x) AND (b < y))

Greater than or equal:

mysql>

-> 1`SELECT 2 >= 2;`

For row comparisons,

`(a, b) >= (x, y)`

is equivalent to:(a > x) OR ((a = x) AND (b >= y))

Greater than:

mysql>

-> 0`SELECT 2 > 2;`

For row comparisons,

`(a, b) > (x, y)`

is equivalent to:(a > x) OR ((a = x) AND (b > y))

If

is greater than or equal to`expr`

and`min`

is less than or equal to`expr`

,`max`

`BETWEEN`

returns`1`

, otherwise it returns`0`

. This is equivalent to the expression`(`

if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 12.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.<=`min`

AND`expr`

<=`expr`

)`max`

mysql>

-> 1, 0 mysql>`SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1;`

-> 0 mysql>`SELECT 1 BETWEEN 2 AND 3;`

-> 1 mysql>`SELECT 'b' BETWEEN 'a' AND 'c';`

-> 1 mysql>`SELECT 2 BETWEEN 2 AND '3';`

-> 0`SELECT 2 BETWEEN 2 AND 'x-3';`

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`

.This is the same as

`NOT (`

.BETWEEN`expr`

AND`min`

)`max`

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>

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

-> NULL`SELECT COALESCE(NULL,NULL,NULL);`

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

`LEAST()`

.mysql>

-> 2 mysql>`SELECT GREATEST(2,0);`

-> 767.0 mysql>`SELECT GREATEST(34.0,3.0,5.0,767.0);`

-> 'C'`SELECT GREATEST('B','A','C');`

`GREATEST()`

returns`NULL`

if any argument is`NULL`

.Returns

`1`

(true) ifis equal to any of the values in the`expr`

`IN()`

list, else returns`0`

(false).Type conversion takes place according to the rules described in Section 12.2, “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, andcan be compared to each of them as a value of the same type (possibly after type conversion), an optimization takes place. The values the list are sorted and the search for`expr`

is done using a binary search, which makes the`expr`

`IN()`

operation very quick.mysql>

-> 0 mysql>`SELECT 2 IN (0,3,5,7);`

-> 1`SELECT 'wefwf' IN ('wee','wefwf','weg');`

`IN()`

can be used to compare row constructors:mysql>

-> 1 mysql>`SELECT (3,4) IN ((1,2), (3,4));`

-> 0`SELECT (3,4) IN ((1,2), (3,5));`

You should never mix quoted and unquoted values in an

`IN()`

list because the comparison rules for quoted values (such as strings) and unquoted values (such as numbers) differ. Mixing types may therefore lead to inconsistent results. For example, do not write an`IN()`

expression like this:SELECT val1 FROM tbl1 WHERE val1 IN (1,2,'a');

Instead, write it like this:

SELECT val1 FROM tbl1 WHERE val1 IN ('1','2','a');

Implicit type conversion may produce nonintuitive results:

mysql>

-> 1, 1`SELECT 'a' IN (0), 0 IN ('b');`

In both cases, the comparison values are converted to floating-point values, yielding 0.0 in each case, and a comparison result of 1 (true).

The number of values in the

`IN()`

list is only limited by the`max_allowed_packet`

value.To comply with the SQL standard,

`IN()`

returns`NULL`

not only if the expression on the left hand side is`NULL`

, but also if no match is found in the list and one of the expressions in the list is`NULL`

.`IN()`

syntax can also be used to write certain types of subqueries. See Section 13.2.11.3, “Subqueries with ANY, IN, or SOME”.This is the same as

`NOT (`

.IN (`expr`

,...))`value`

Returns

`0`

if<`N`

,`N1`

`1`

if<`N`

and so on or`N2`

`-1`

ifis`N`

`NULL`

. All arguments are treated as integers. It is required that<`N1`

<`N2`

<`N3`

`...`

<for this function to work correctly. This is because a binary search is used (very fast).`Nn`

mysql>

-> 3 mysql>`SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);`

-> 2 mysql>`SELECT INTERVAL(10, 1, 10, 100, 1000);`

-> 0`SELECT INTERVAL(22, 23, 30, 44, 200);`

Tests a value against a boolean value, where

can be`boolean_value`

`TRUE`

,`FALSE`

, or`UNKNOWN`

.mysql>

-> 1, 1, 1`SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;`

Tests a value against a boolean value, where

can be`boolean_value`

`TRUE`

,`FALSE`

, or`UNKNOWN`

.mysql>

-> 1, 1, 0`SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;`

Tests whether a value is

`NULL`

.mysql>

-> 0, 0, 1`SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;`

To work well with ODBC programs, MySQL supports the following extra features when using

`IS NULL`

:If

`sql_auto_is_null`

variable is set to 1, then after a statement that successfully inserts an automatically generated`AUTO_INCREMENT`

value, you can find that value by issuing a statement of the following form:SELECT * FROM

WHERE`tbl_name`

IS NULL`auto_col`

If the statement returns a row, the value returned is the same as if you invoked the

`LAST_INSERT_ID()`

function. For details, including the return value after a multiple-row insert, see Section 12.15, “Information Functions”. If no`AUTO_INCREMENT`

value was successfully inserted, the`SELECT`

statement returns no row.The behavior of retrieving an

`AUTO_INCREMENT`

value by using an`IS NULL`

comparison can be disabled by setting`sql_auto_is_null = 0`

. See Section 5.1.8, “Server System Variables”.The default value of

`sql_auto_is_null`

is 0.For

`DATE`

and`DATETIME`

columns that are declared as`NOT NULL`

, you can find the special date`'0000-00-00'`

by using a statement like this:SELECT * FROM

WHERE`tbl_name`

IS NULL`date_column`

This is needed to get some ODBC applications to work because ODBC does not support a

`'0000-00-00'`

date value.See Obtaining Auto-Increment Values, and the description for the

`FLAG_AUTO_IS_NULL`

option at Connector/ODBC Connection Parameters.

Tests whether a value is not

`NULL`

.mysql>

-> 1, 1, 0`SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;`

If

is`expr`

`NULL`

,`ISNULL()`

returns`1`

, otherwise it returns`0`

.mysql>

-> 0 mysql>`SELECT ISNULL(1+1);`

-> 1`SELECT ISNULL(1/0);`

`ISNULL()`

can be used instead of`=`

to test whether a value is`NULL`

. (Comparing a value to`NULL`

using`=`

always yields`NULL`

.)The

`ISNULL()`

function shares some special behaviors with the`IS NULL`

comparison operator. See the description of`IS NULL`

.With two or more arguments, returns the smallest (minimum-valued) argument. The arguments are compared using the following rules:

If any argument is

`NULL`

, the result is`NULL`

. No comparison is needed.If all arguments are integer-valued, they are compared as integers.

If at least one argument is double precision, they are compared as double-precision values. Otherwise, if at least one argument is a

`DECIMAL`

value, they are compared as`DECIMAL`

values.If the arguments comprise a mix of numbers and strings, they are compared as strings.

If any argument is a nonbinary (character) string, the arguments are compared as nonbinary strings.

In all other cases, the arguments are compared as binary strings.

The return type of

`LEAST()`

is the aggregated type of the comparison argument types.mysql>

-> 0 mysql>`SELECT LEAST(2,0);`

-> 3.0 mysql>`SELECT LEAST(34.0,3.0,5.0,767.0);`

-> 'A'`SELECT LEAST('B','A','C');`