MySQL in a Nutshell, 2nd Edition

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. Table B-2 lists the logical and relational operators allowed in MySQL.

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 value is NULL, though, NULL will be returned. The <=> operator is used to compare values for equality; 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.

Table B-2. Relational operators

Operator	Use
<	Less than
>	Greater than
<=	Less than or equal to
>=	Greater than or equal to
expression BETWEEN n AND n	Between first and second number
expression NOT 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
SOUNDS LIKE	Matches a sound pattern (see SOUNDEX() function described in Chapter 11)
NOT LIKE	Doesn’t match a pattern
REGEXP, RLIKE	Matches a regular expression
NOT REGEXP	Doesn’t match ...