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.
