### 11.3.2. Comparison Functions and Operators

Table 11.3. Comparison Operators

NameDescription
`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 NULL`NOT NULL value test
`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:

```=  >  <  >=  <=  <>  !=
```

For examples of row comparisons, see Section 12.2.10.5, “Row Subqueries”.

Some of the functions in this section return values other than `1` (`TRUE`), `0` (`FALSE`), or `NULL`. For example, `LEAST()` and `GREATEST()`. However, the value they return is based on comparison operations performed according to the rules described in Section 11.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 11.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.

• Equal:

```mysql> `SELECT 1 = 0;`
-> 0
mysql> `SELECT '0' = 0;`
-> 1
mysql> `SELECT '0.0' = 0;`
-> 1
mysql> `SELECT '0.01' = 0;`
-> 0
mysql> `SELECT '.01' = 0.01;`
-> 1
```
• `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`.

```mysql> `SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;`
-> 1, 1, 0
mysql> `SELECT 1 = 1, NULL = NULL, 1 = NULL;`
-> 1, NULL, NULL
```
• Not equal:

```mysql> `SELECT '.01' <> '0.01';`
-> 1
mysql> `SELECT .01 <> '0.01';`
-> 0
mysql> `SELECT 'zapp' <> 'zappp';`
-> 1
```
• Less than or equal:

```mysql> `SELECT 0.1 <= 2;`
-> 1
```
• Less than:

```mysql> `SELECT 2 < 2;`
-> 0
```
• Greater than or equal:

```mysql> `SELECT 2 >= 2;`
-> 1
```
• Greater than:

```mysql> `SELECT 2 > 2;`
-> 0
```
• Tests a value against a boolean value, where `boolean_value` can be `TRUE`, `FALSE`, or `UNKNOWN`.

```mysql> `SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;`
-> 1, 1, 1
```
• Tests a value against a boolean value, where `boolean_value` can be `TRUE`, `FALSE`, or `UNKNOWN`.

```mysql> `SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;`
-> 1, 1, 0
```
• Tests whether a value is `NULL`.

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

To work well with ODBC programs, MySQL supports the following extra features when using ```IS NULL```:

• Tests whether a value is not `NULL`.

```mysql> `SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;`
-> 1, 1, 0
```
• 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 11.2, “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`.

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

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

```mysql> `SELECT COALESCE(NULL,1);`
-> 1
mysql> `SELECT COALESCE(NULL,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> `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`.

• Returns `1` if `expr` is equal to any of the values in the `IN` list, else returns `0`. If all values are constants, they are evaluated according to the type of `expr` and sorted. The search for the item then is done using a binary search. This means `IN` is very quick if the `IN` value list consists entirely of constants. Otherwise, type conversion takes place according to the rules described in Section 11.2, “Type Conversion in Expression Evaluation”, but applied to all the arguments.

```mysql> `SELECT 2 IN (0,3,5,7);`
-> 0
mysql> `SELECT 'wefwf' IN ('wee','wefwf','weg');`
-> 1
```

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');
```

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

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 12.2.10.3, “Subqueries with `ANY`, `IN`, or `SOME`.

• This is the same as ```NOT (expr IN (value,...))```.

• If `expr` is `NULL`, `ISNULL()` returns `1`, otherwise it returns `0`.

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

`ISNULL()` can be used instead of `=` to test whether a value is `NULL`. (Comparing a value to `NULL` using `=` always yields false.)

The `ISNULL()` function shares some special behaviors with the `IS NULL` comparison operator. See the description of `IS NULL`.

• Returns `0` if `N` < `N1`, `1` if `N` < `N2` and so on or `-1` if `N` is `NULL`. All arguments are treated as integers. It is required that `N1` < `N2` < `N3` < `...` < `Nn` for this function to work correctly. This is because a binary search is used (very fast).

```mysql> `SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);`
-> 3
mysql> `SELECT INTERVAL(10, 1, 10, 100, 1000);`
-> 2
mysql> `SELECT INTERVAL(22, 23, 30, 44, 200);`
-> 0
```
• 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 the return value is used in an `INTEGER` context or all arguments are integer-valued, they are compared as integers.

• If the return value is used in a `REAL` context or all arguments are real-valued, they are compared as reals.

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

• 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.

```mysql> `SELECT LEAST(2,0);`
-> 0
mysql> `SELECT LEAST(34.0,3.0,5.0,767.0);`
-> 3.0
mysql> `SELECT LEAST('B','A','C');`
-> 'A'
```

Note that the preceding conversion rules can produce strange results in some borderline cases:

```mysql> `SELECT CAST(LEAST(3600, 9223372036854775808.0) as SIGNED);`
-> -9223372036854775808
```

This happens because MySQL reads `9223372036854775808.0` in an integer context. The integer representation is not good enough to hold the value, so it wraps to a signed integer.

Copyright © 2010-2020 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout