MySQL | Operator precedence

Last Updated : 04 Feb, 2019

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.

Python MySQL - BETWEEN and IN Operator

Tanvi_Garg

Improve

Article Tags :

Similar Reads

RLIKE operator in MySQL

RLIKE : This operator in MySQL is used to performs a pattern match of a string expression against a pattern. Syntax : RLIKE pattern Parameters : This method accepts one parameter as mentioned in syntax. pattern -The pattern which we want to match against an expression. Various pattern and their usag

2 min read

Queries using AND ,OR ,NOT operators in MySQL

AND, OR, NOT operators are basically used with WHERE clause in order to retrieve data from table by filtering with some conditions using AND, OR, NOT in MySQL.Here in this article let us see different queries on the student table using AND, OR, NOT operators step-by-step. Step-1:Creating a database

2 min read

Python MySQL - BETWEEN and IN Operator

In this article, we are going to see the database operations BETWEEN and IN operators in MySQL using Python. Both of these operators are used with the WHERE query to check if an expression is within a range or in a list of values in SQL. We are going to use the below table to perform various operati

3 min read

SQL LIKE Operator

The SQL LIKE operator is used for performing pattern-based searches in a database. It is used in combination with the WHERE clause to filter records based on specified patterns, making it essential for any database-driven application that requires flexible search functionality. In this article, we w

5 min read

SQL NOT EQUAL Operator

The SQL NOT EQUAL operator is a comparison operator used to check if two expressions are not equal to each other. It helps filter out records that match certain conditions, making it a valuable tool in SQL queries. In this article, We will explore the SQL NOT EQUAL operator, including its syntax, us

4 min read

SQL OR Operator

The SQL OR operator is a powerful tool used to filter records in a database by combining multiple conditions in the WHERE clause. When using OR, a record will be returned if any of the conditions connected by the operator are true. This allows for more flexibility in querying and is important when w

7 min read

SQL IN Operator

The SQL IN operator filters data based on a list of specific values. In general, we can only use one condition in the Where clause, but the IN operator allows us to specify multiple values. In this article, we will learn about the IN operator in SQL by understanding its syntax and examples. IN Opera

4 min read

SQL Comparison Operators

SQL Comparison Operators are used to compare two values and check if they meet the specific criteria. Some comparison operators are = Equal to, > Greater than , < Less than, etc. Comparison Operators in SQLThe below table shows all comparison operators in SQL : OperatorDescription=The SQL Equa

3 min read

SQL AND Operator

In SQL, the AND operator is an essential tool used to combine multiple conditions in a WHERE clause. This allows us to filter records based on multiple criteria, making our queries more specific and tailored to our needs. When used correctly, the AND operator can help us retrieve data that satisfies

5 min read

SQL NOT Operator

The SQL NOT Operator is a logical operator used to negate or reverse the result of a condition in SQL queries. It is commonly used with the WHERE clause to filter records that do not meet a specified condition, helping you exclude certain values from your results. In this article, we will learn ever

3 min read