Open In App

MySQL | Operator precedence

Improve
Improve
Like Article
Like
Save
Share
Report

Operator precedence specifies the order in which operators are evaluated when two or more operators with different precedence are adjacent in an expression.

For example, 1+2/3 gives the different result as compared to (1+2)/3. Just like all other programming languages C, C++, Java etc. MySQL also has a precedence rule.

The following table describes operator precedence in MySQL, from highest to lowest. Operators which are in the same group have the equal precedence.

Operator Description
INTERVAL Return the index of the argument that is less than the first argument
BINARY
COLLATE
This is a type that stores binary byte strings
This clause override whatever the default collation is for comparison
! Negate values

~
It change the sign of the operand
It inverts the bits of operand
^ Bitwise XOR
*
/
DIV
%, MOD
Multiplication operator
Division operator
Integer Division (discard the fractional part of division)
Modulo operator

+
Minus operator
Addition operator
<<
>>
Shift a (BIGINT) number or binary string to left
Shift a (BIGINT) number or binary string to right
& Bitwise AND
| Bitwise OR
=
<=>
>=, >
<=, <
<>, !=
IS
LIKE
REGEXP
IN
Comparison operator
NULL-safe equal to operator
Greater than/Greater than or equal to
Less than/Less than or equal to
Not Equal to operator
Test a value against a boolean value
Pattern matching operator
Matches the string expression with the regular expression
Check whether a value is present in list or not
BETWEEN
CASE WHEN THEN ELSE
Check whether a value is within a range of values
Case operator
NOT Negates Value
AND, && Logical AND
XOR Logical XOR
OR, || Logical OR
=
:=
Assign a value (as part of a SET statement/SET clause in an UPDATE statement)
Assign a value

These operator precedence rule greatly affects our MySQL queries.Without knowledge of operator precedence we can get unexpected result. To, understand this consider the following table Student.

id name marks
1 Payal 12
2 Utkarsh 9
3 Reeta 19
4 Sunny 15
5 Shanu 5
6 Punit 7

From the above table we want the result of those students having marks greater than 10 and whose name starts with either ‘p’ or ‘s’. So, it’s query can be written as-

mysql>select * 
from student 
where marks>10 and name like 'p%' 
                   or name like 's%'; 

Result:
It will produce the desired result:

id name marks
1 Payal 12
4 Sunny 15
6 Punit 7

This result set is not as expected from the query. As it is giving the result of a student ‘Punit’ having marks less than 10 which is not required. Due to higher precedence of operator AND as compare to OR, the above query give result of all those students having marks greater than 10 and name starting with ‘s’, in addition to those the result of those student having name starting with ‘p’ is also given in output. So, here come role of parentheses. The above-stated query can be written as,

mysql>select * 
from student 
where marks>10 and (name like 'p%' 
                       or name like 's%');

Result:
It will produce the desired result:

id name marks
1 Payal 12
4 Sunny 15


Hence, this precedence of the operator can be overridden by making use of parenthesis.


Last Updated : 04 Feb, 2019
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads