Relational Operators
Relational
operators are used for comparing numbers and strings. If a string is
compared to a number, MySQL will try to convert the string to a
number. If a TIMESTAMP
column is compared to a
string or a number, MySQL will attempt to convert the string or
number to a timestamp value. If it’s unsuccessful at
converting the other value to a timestamp, it will convert the
TIMESTAMP
column’s value to a
string or a number. TIME
and
DATE
columns are compared to other values as
strings. The logical and relational operators allowed are listed in
Table B-2.
Operator |
Use |
< |
Less than |
> |
Greater than |
<= |
Less than or equal to |
>= |
Greater than or equal to |
expressionBETWEEN n AND n |
Between first and second number |
expressionNOT BETWEEN n AND n |
Not between first and second number |
IN (...) |
In a set |
NOT IN (...) |
Not in a set |
= |
Equal to |
<=> |
Equal to (for comparing NULL values) |
LIKE |
Matches a pattern |
NOT LIKE |
Doesn’t match a pattern |
REGEXP, RLIKE |
Matches a regular expression |
!= |
Not equal to |
<> |
Not equal to |
IS NULL |
NULL |
IS NOT NULL |
Not NULL |
The minus sign may be used for subtracting numbers or for setting a
number to a negative. The equals sign is used to compare two values.
If one is NULL, though, NULL will be returned. The
<=>
operator is used to compare for equality
and it’s NULL-safe. For example, an SQL statement
containing something like IF(col1
<=> col2)
where the values of both are NULL will return 1 and not NULL. ...
Get MySQL in a Nutshell now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.