MySQL | Operator precedence
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 |