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.

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();

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.