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 a regular expression
`!=`	Not equal to
`<>`	Not equal to
`NOT`, `!`	Negates
`IS NULL`	NULL
`IS NOT NULL`	Not NULL

Table of Contents for MySQL in a Nutshell, 2nd Edition

Relational Operators

Table of Contents for
MySQL in a Nutshell, 2nd Edition