Comparison Operators
|
|
Comparison operators compare values and return true or false (1 or 0) depending on the results. If there's a null value, the operator will return NULL as a result in most cases. Different types can be compared (strings, numbers, dates, and so on), though if the types are different, you need to be careful. MySQL converts the types to an equivalent as well as it can.
If you're comparing strings, they are compared case insensitively, unless they are BINARY. For example, A is the same as a, but BINARY A is not the same as BINARY a. Capitals come first in this case, so BINARY A is less than BINARY a. Similarly, the string 10 is less than the string 2 because, being a string, it's compared from left to right. The first check is then whether 1 is less than 2, which it is, and the check stops there (the same way as az is before b).
=
value1 = value2
True if both value1 and value2 are equal. If either is null, this will return NULL.
For example:
mysql> SELECT 1=2; +-----+ | 1=2 | +-----+ | 0 | +-----+ mysql> SELECT 'A' = 'a'; +-----------+ | 'A' = 'a' | +-----------+ | 1 | +-----------+ mysql> SELECT BINARY 'a' = 'A'; +------------------+ | BINARY 'a' = 'A' | +------------------+ | 0 | +------------------+ mysql> SELECT NULL=NULL; +-----------+ | NULL=NULL | +-----------+ | NULL | +-----------+
!=, <>
value1 <> value2 value1 != value2
True if value1 is not equal to value2.
For example:
mysql> SELECT 'a' != 'A'; +------------+ | 'a' != 'A' | +------------+ | 0 | +------------+ mysql> SELECT BINARY 'a' <> 'A'; +-------------------+ | BINARY 'a' <> 'A' | +-------------------+ | 1 | +-------------------+
>
value1 > value2
True if value1 is greater than value2.
For example:
mysql> SELECT 1>2; +-----+ | 1>2 | +-----+ | 0 | +-----+ mysql> SELECT 'b'>'a'; +---------+ | 'b'>'a' | +---------+ | 1 | +---------+
<
value1 < value2
True if value1 is less than value2.
For example:
mysql> SELECT 'b' < 'd'; +-----------+ | 'b' < 'd' | +-----------+ | 1 | +-----------+ mysql> SELECT '4' < '34'; +------------+ | '4' < '34' | +------------+ | 0 | +------------+
>=
value1 >= value2
True if value1 is greater than or equal to value2.
For example:
mysql> SELECT 4 >= 4; +--------+ | 4 >= 4 | +--------+ | 1 | +--------+
<=
value1<= value2
True if value1 is less than or equal to value2.
For example:
mysql> SELECT 4 <= 3; +--------+ | 4 <= 3 | +--------+ | 0 | +--------+
<=>
value1 <=> value2
True if value1 is equal to value2, including nulls. This allows you to pretend that NULL is actually some value, and therefore get a true or false result (rather than NULL) when using NULLs in comparison with non-NULLs; by contrast, MySQL refuses to give a definite answer to the question, "Is 4 equal to NULL?" Instead, it correctly says that the expression 4 = NULL evaluates to something undecidable (NULL).
For example:
mysql> SELECT NULL<=>NULL; +-------------+ | NULL<=>NULL | +-------------+ | 1 | +-------------+ mysql> SELECT 4 <=> NULL; +------------+ | 4 <=> NULL | +------------+ | 0 | +------------+
IS NULL
value1 IS NULL
True if value1 is null (not false).
For example:
mysql> SELECT NULL IS NULL; +--------------+ | NULL IS NULL | +--------------+ | 1 | +--------------+ mysql> SELECT 0 IS NULL; +-----------+ | 0 IS NULL | +-----------+ | 0 | +-----------+
BETWEEN
value1 BETWEEN value2 AND value3
True if value1 is inclusively between value2 and value3.
For example:
mysql> SELECT 1 BETWEEN 0 AND 2; +-------------------+ | 1 BETWEEN 0 AND 2 | +-------------------+ | 1 | +-------------------+ mysql> SELECT 'a' BETWEEN 'A' and 'B'; +-------------------------+ | 'a' BETWEEN 'A' and 'B' | +-------------------------+ | 1 | +-------------------------+ mysql> SELECT BINARY 'a' BETWEEN 'A' AND 'C'; +--------------------------------+ | BINARY 'a' BETWEEN 'A' AND 'C' | +--------------------------------+ | 0 | +--------------------------------+
LIKE
value1 LIKE value2
True if value1 matches value2 on an SQL pattern match. A percentage (%) refers to any number of characters, and an underscore (_) refers to one character.
For example:
mysql> SELECT 'abc' LIKE 'ab_'; +------------------+ | 'abc' LIKE 'ab_' | +------------------+ | 1 | +------------------+ mysql> SELECT 'abc' LIKE '%c'; +-----------------+ | 'abc' LIKE '%c' | +-----------------+ | 1 | +-----------------+
IN
value1 IN (value2 [value3,...])
True if value1 is equal to any value in the comma-separated list.
For example:
mysql> SELECT 'a' IN('b','c','aa'); +----------------------+ | 'a' IN('b','c','aa') | +----------------------+ | 0 | +----------------------+ mysql> SELECT 'a' IN('A','B'); +-----------------+ | 'a' IN('A','B') | +-----------------+ | 1 | +-----------------+
REGEXP, RLIKE
value1 REGEXP value2 value1 RLIKE value2
True if value1 matches value2 with a regular expression. Table B.1 lists the regular expression characters.
| Character | Description |
|---|---|
| * | Matches zero or more instances of the subexpression preceding it |
| + | Matches one or more instances of the subexpression preceding it |
| ? | Matches zero or one instances of the subexpression preceding it |
| Continued on next page | |
| . | Matches any single character |
| [xyz] | Matches any of x, y, or z (the characters within the brackets) |
| [AZ] | Matches any uppercase letter |
| [az] | Matches any lowercase letter |
| [09] | Matches any digit |
| ^ | Anchors the match from the beginning |
| $ | Anchors the match to the end |
| | | Separates subexpressions in the regular expression |
| {n,m} | Subexpression must occur at least n times, but no more than n |
| {n} | Subexpression must occur exactly n times |
| {n,| | Subexpression must occur at least n times |
| () | Groups characters into subexpressions |
For example:
mysql> SELECT 'pqwxyz' REGEXP 'xyz'; +-----------------------+ | 'pqwxyz' REGEXP 'xyz' | +-----------------------+ | 1 | +-----------------------+ mysql> SELECT 'xyz' REGEXP '^x'; +-------------------+ | 'xyz' REGEXP '^x' | +-------------------+ | 1 | +-------------------+ mysql> SELECT 'abcdef' REGEXP 'g+'; +----------------------+ | 'abcdef' REGEXP 'g+' | +----------------------+ | 0 | +----------------------+ mysql> SELECT 'abcdef' REGEXP 'g*'; +----------------------+ | 'abcdef' REGEXP 'g*' | +----------------------+ | 1 | +----------------------+ mysql> SELECT 'ian' REGEXP 'iai*n'; +----------------------+ | 'ian' REGEXP 'iai*n' | +----------------------+ | 1 | +----------------------+ mysql> SELECT 'aaaa' REGEXP 'a{3,}'; +-----------------------+ | 'aaaa' REGEXP 'a{3,}' | +-----------------------+ | 1 | +-----------------------+ mysql> SELECT 'aaaa' REGEXP '^aaa$'; +-----------------------+ | 'aaaa' REGEXP '^aaa$' | +-----------------------+ | 0 | +-----------------------+ mysql> SELECT 'abcabcabc' REGEXP 'abc{3}'; +-----------------------------+ | 'abcabcabc' REGEXP 'abc{3}' | +-----------------------------+ | 0 | +-----------------------------+ mysql> SELECT 'abcabcabc' REGEXP '(abc){3}'; +-------------------------------+ | 'abcabcabc' REGEXP '(abc){3}' | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT 'abcbbcccc' REGEXP '[abc]{3}'; +-------------------------------+ | 'abcbbcccc' REGEXP '[abc]{3}' | +-------------------------------+ | 1 | +-------------------------------+ mysql> SELECT 'abcbbcccc' REGEXP '(a|b|c){3}'; +---------------------------------+ | 'abcbbcccc' REGEXP '(a|b|c){3}' | +---------------------------------+ | 1 | +---------------------------------+
|
|
