Like any other programming language, SQL carries among its core
elements operators and named procedures. This reference lists all
those operators and functions and explains how they evaluate into
useful expressions.
17.1. Operators
MySQL
offers
three kinds of operators: arithmetic, comparison, and logical.
17.1.1. Rules of Precedence
When your SQL contains complex expressions,
the subexpressions are evaluated based on MySQL's
rules of precedence. Of course, you may always override
MySQL's rules of precedence by enclosing an
expression in parentheses.
-
BINARY
-
NOT
-
- (unary minus)
-
* / %
-
+ -
-
<< >>
-
&
-
|
-
< <= > >= = <=> <> IN IS LIKE
REGEXP
-
BETWEEN
-
AND
-
OR
17.1.2. Arithmetic Operators
Arithmetic operators perform basic
arithmetic on two values.
- +
-
Adds two numerical values
- -
-
Subtracts two numerical values
- *
-
Multiplies two numerical values
- /
-
Divides two numerical values
- %
-
Gives the modulo of two numerical values
- |
-
Performs a bitwise
OR on two integer values
- &
-
Performs a bitwise
AND on two integer values
- <<
-
Performs a
bitwise left shift on an integer value
- >>
-
Performs a bitwise right shift on
an integer value
17.1.3. Comparison Operators
Comparison operators compare values
and return 1 if the comparison is true and 0 otherwise. Except for
the <==> operator, NULL
values cause a comparison operator to evaluate to
NULL.
- <> or !=
-
Match rows if the
two values are not equal.
- <=
-
Match rows if the left value is less
than or equal to the right value.
- <
-
Match rows if the
left value is less than the right value.
- >=
-
Match rows if the left value is greater
than or equal to the right value.
- >
-
Match rows if the
left value is greater than the right value.
- value BETWEEN value1 AND value2
-
Match rows if
value is between
value1 and
value2, or equal to one of them.
- value IN (value1,value2,...)
-
Match rows if value is among the values
listed.
- value NOT IN (value1, value2,...)
-
Match rows if value is not among the
values listed.
- value1 LIKE value2
-
Compares value1 to
value2 and matches the rows if they match.
The righthand value can contain the wildcard '%',
which matches any number of characters (including 0), and
'_', which matches exactly one character. This is
probably the single most used comparison in SQL. Its most common use
is comparing a field value with a literal containing a wildcard
(e.g., SELECT name
FROM people
WHERE name
LIKE 'B%').
- value1 NOT LIKE value2
-
Compares value1 to
value2 and matches the rows if they
differ. This is identical to NOT (value1 LIKE
value2).
- value1 REGEXP/RLIKE value2
-
Compares value1 to
value2 using the extended regular
expression syntax and matches the rows if the two values match. The
righthand value can contain full Unix regular expression wildcards
and constructs (e.g., SELECT
name FROM
people WHERE name
RLIKE '^B.*').
- value1 NOT REGEXP value2
-
Compares value1 to
value2 using the extended regular
expression syntax and matches the rows if they differ. This is
identical to NOT (value1 REXEXP value2).
17.1.4. Logical Operators
Logical
operators check the truth value of one or more expressions. In SQL
terms, a logical operator checks whether its operands are 0, nonzero,
or NULL. A 0 value means false, nonzero means
true, and NULL means no value.
- NOT or !
-
Performs a logical
not (returns 1 if the value is 0 or
NULL; otherwise, returns 0)
- OR or ||
-
Performs a logical
or (returns 1 if any of the arguments are nonzero
and non-NULL; otherwise, returns 0)
- AND or &&
-
Performs a logical
and (returns 0 if any of the arguments are 0 or
NULL; otherwise, returns 1)
