Arithmetic Operators in MySQL

Introduction

Arithmetic operators in MySQL are used to perform mathematical calculations on numerical values in queries. There are several arithmetic operators in MySQL, including addition (+), subtraction (-), multiplication (*), division (/), and modulus (%).

These operators can be used in combination with other SQL functions to create complex queries. It's important to note that MySQL follows the standard order of operations when evaluating expressions, so it's important to use parentheses to ensure that calculations are performed in the correct order. We will read about the order of operations further in this article.

Let's see more about these operators:

• Addition (+): The addition operator is used to add two or more values together. It can be used to add numeric values, date, and time values, or string values.
• Subtraction (-): The subtraction operator is used to subtract one value from another. It is primarily used with numeric values, but it can also be used with date and time values to calculate differences.
• Multiplication (*): The multiplication operator is used to multiply two or more values together. It can be used with numeric values, but it can also be used with date and time values to calculate durations.
• Division (/): The division operator is used to divide one value by another. It is primarily used with numeric values, but it can also be used with date and time values to calculate rates or proportions.
• Modulus (%): The modulus operator returns the remainder of a division operation. It is used primarily with numeric values, but it can also be used with date and time values to calculate intervals.

In addition to this basic arithmetic operator, MySQL also supports several mathematical functions, such as ABS, CEILING, FLOOR, ROUND, and TRUNCATE, that can be used to perform more advanced calculations.

Addition Operator (+)

The addition operator is an Arithmetic operator in MySQL, represented by the plus sign (+), and it is used to add two or more values together. It can be used with both numeric and non-numeric values, such as strings or dates, to concatenate or add values.

Syntax

In this syntax, value1 and value2 are the two values that will be added together using the addition operator. These values can be columns in a table, constants, or expressions that evaluate a value.

Examples

Example 1:

In this example, the query will return the result of adding the values 10 and 5, which is 15.

Example 2: We can also use the addition operator to concatenate strings in MySQL, like this:

This query will return a result of 'HelloWorld', which is the concatenation of the two strings 'Hello' and 'World'.

Example 3: We can use the addition operator to add or concatenate dates in MySQL, like this:

This query will return the date and time that is one day after the current date and time. The N OW() function returns the current date and time, and the INTERVAL 1 DAY expression adds one day to that date and time.

Subtraction Operator (-)

The subtraction operator is an Arithmetic operator in MySQL, represented by the minus sign (-), and it is used to subtract one value from another. It can be used with both numeric and non-numeric values, such as dates or timestamps, to calculate differences.

Syntax

In this syntax, value1 is the value that will be subtracted from, and value2 is the value that will be subtracted. These values can be columns in a table, constants, or expressions that evaluate a value.

Examples

Example 1:

This query will return a result of 5, which is the difference between 10 and 5.

Example 2: We can also use the subtraction operator to calculate the difference between two dates or timestamps, like this:

This query will return a result of 365, which is the number of days between January 1, 2021, and January 1, 2022.

Example 3: We can use the subtraction operator with time values to calculate durations or intervals, like this:

This query will return a result of '02:30:00', which is the duration between 10:00 AM and 12:30 PM.

Multiplication Operator (*)

The multiplication operator is an Arithmetic operator in MySQL, represented by the asterisk symbol (*), and it is used to multiply two or more values together. It can be used with numeric values, and can also be used with dates or timestamps to calculate durations.

Syntax

In this syntax, value1 and value2 are the two values that will be multiplied together using the multiplication operator. These values can be columns in a table, constants, or expressions that evaluate a value.

Example

Example 1:

This query will return a result of 50, which is the product of 10 and 5. Example 2: We can also use the multiplication operator with dates or timestamps to calculate durations or intervals, like this:

This query will return a result of '05:00:00', which is 2.5 hours multiplied by 2.

Division Operator (/)

The division operator is an Arithmetic operator in MySQL used to perform a division operation between two numeric values.

Syntax

where dividend is the numeric value to be divided and divisor is the numeric value by which dividend is to be divided.

Example

Example 1:

In this example, the division operator / is used to divide 10 by 5, which results in 2. Example 2:

In this example, the division operator / is used to divide the salary of each employee by 12, which results in their monthly salary. The SELECT statement is used to display the result of the division operation, which is aliased as monthly_salary.

It is important to note that if the divisor is 0, a division by zero error will occur. Therefore, it is important to ensure that the divisor is not 0 before performing a division operation.

Modulo Operator (%)

The modulo operator is an Arithmetic operator in MySQL used to find the remainder of a division operation between two numeric values.

Syntax

where dividend is the numeric value to be divided and divisor is the numeric value by which dividend is to be divided.

Example

Example 1:

In this example, the modulo operator % is used to find the remainder of dividing 10 by 3, which is 1. The SELECT statement is used to display the result of the modulo operation.

Example 2:

In this example, the modulo operator % is used to find the remainder of dividing the id of each customer by 2. If the remainder is 0, it means that the id is an even number, so the result of the modulo operation is 0. If the remainder is 1, it means that the id is an odd number, so the result of the modulo operation is 1. The SELECT statement is used to display the result of the modulo operation, which is aliased as is_even.

Order of Operations

Explanation of the order of operations in MySQL

Arithmetic operators in MySQL follow the standard order of operations or precedence, which is a set of rules that determine the order in which operations are evaluated. The order of operations in MySQL arithmetic operators is as follows:

• Parentheses: Operations inside parentheses are performed first. Any operations inside parentheses are evaluated before any other operation in the expression. This allows you to group expressions and evaluate them as a single unit. For example: Consider the expression 3 + 2 * 4. Without parentheses, the multiplication would be performed before the addition, resulting in 11. However, by using parentheses to group the addition operation, like this (3 + 2) * 4, the addition operation is performed first, resulting in 20.
• Exponents: Exponential operations (such as raising a number to power) are performed next. Exponential operations are performed after any operations inside parentheses. For example: Consider the expression 2 + 3^2. In this case, the exponent operation 3^2 is evaluated first, resulting in 9. Then the addition operation is performed, resulting in 11.
• Multiplication and Division: Multiplication and division operations are performed from left to right. After parentheses and exponents, multiplication and division operations are performed in order from left to right. For example: Consider the expression 2 + 6 / 3 * 4. In this case, the division operation 6 / 3 is performed first, resulting in 2. Then, the multiplication operation 2 * 4 is performed, resulting in 8. Finally, the addition operation 2 + 8 is performed, resulting in 10.
• Addition and Subtraction: Addition and subtraction operations are performed from left to right. After parentheses, exponents, multiplication, and division, addition and subtraction operations are performed in order from left to right. For example: Consider the expression 10 - 5 + 2. In this case, the subtraction operation 10 - 5 is performed first, resulting in 5. Then, the addition operation 5 + 2 is performed, resulting in 7.

It is important to note that if there are operations of the same precedence level, they are performed in order from left to right.

In summary, the order of operations in Arithmetic operators in MySQL is parentheses, exponents, multiplication, and division (performed from left to right), and addition and subtraction (performed from left to right).

Examples of how the order of operations can impact results

Sure, here are some examples of how the order of operations can impact results in MySQL:

Example 1:

In this example, MySQL follows the order of operations and performs the multiplication operation first, resulting in 15. Then, it adds 6 to 15, resulting in 21.

In this example, we use parentheses to group the addition operation, forcing MySQL to perform the addition operation first, resulting in 11. Then, it multiplies 11 by 3, resulting in 33.

Example 2:

In this example, MySQL follows the order of operations and performs the division operation first, resulting in 4. Then, it multiplies 4 by 4, resulting in 16.

In this example, we use parentheses to group the multiplication operation, forcing MySQL to perform the multiplication operation first, resulting in 8. Then, it divides 8 by 2 * 4, resulting in 1.

See Also

• Logical operators
• Comparison operators