Operator Precedence
The precedence is the order in which the SQL operators are evaluated.
The following list shows the SQL operator precedence. Operators that appear first in the list have a higher precedence. Operators which are listed together have the same precedence.
INTERVAL
BINARY
,COLLATE
!
-
(unary minus), [[bitwise-not|]] (unary bit inversion)||
(string concatenation)^
*
,/
,DIV
,%
,MOD
-
,+
<<
,>>
&
|
=
(comparison),<=>
,>=
,>
,<=
,<
,<>
,!=
,IS
,LIKE
,REGEXP
,IN
BETWEEN
,CASE
,WHEN
,THEN
,ELSE
,END
NOT
&&
,AND
XOR
||
(logical or),OR
=
(assignment),:=
Functions precedence is always higher than operators precedence.
In this page CASE
refers to the CASE operator, not to the CASE statement.
If the HIGH_NOT_PRECEDENCE
SQL_MODE is set, NOT
has the same precedence as !
.
The ||
operator's precedence, as well as its meaning, depends on the PIPES_AS_CONCAT
SQL_MODE flag: if it is on, ||
can be used to concatenate strings (like the CONCAT() function) and has a higher precedence.
The =
operator's precedence depends on the context - it is higher when =
is used as a comparison operator.
Parenthesis can be used to modify the operators precedence in an expression.
Short-circuit evaluation
The AND
, OR
, &&
and ||
operators support short-circuit evaluation. This means that, in some cases, the expression on the right of those operators is not evaluated, because its result cannot affect the result. In the following cases, short-circuit evaluation is used and x()
is not evaluated:
FALSE AND x()
FALSE && x()
TRUE OR x()
TRUE || x()
NULL BETWEEN x() AND x()
Note however that the short-circuit evaluation does not apply to NULL AND x()
. Also, BETWEEN
's right operands are not evaluated if the left operand is NULL
, but in all other cases all the operands are evaluated.
This is a speed optimization. Also, since functions can have side-effects, this behavior can be used to choose whether execute them or not using a concise syntax:
SELECT some_function() OR log_error();