MySQL - Arithmetic Operators

Arithmetic operators in MySQL are used to perform mathematical calculations on numeric values. These operators can be used in SELECT statements to calculate values on the fly, or in WHERE and UPDATE clauses to filter or modify data based on calculations.

MySQL Arithmetic Operators

Here is a list of the primary arithmetic operators available in MySQL:

Operator	Name	Description
+	Addition	Adds two numeric values together.
-	Subtraction	Subtracts the right-hand operand from the left-hand operand.
*	Multiplication	Multiplies two numeric values.
/	Division	Divides the left-hand operand by the right-hand operand. Returns a floating-point number.
DIV	Integer Division	Performs division and discards the fractional part, returning an integer.
%	Modulus	Returns the integer remainder of a division. `A % B` is equivalent to `MOD(A,B)`.
-	Unary Minus	Changes the sign of the operand.

Examples in Practice

Let’s create a sample products table to demonstrate how these operators work in a real-world context.

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    stock_quantity INT
);

INSERT INTO products VALUES
(1, 'Laptop', 1200.50, 50),
(2, 'Mouse', 25.00, 153),
(3, 'Keyboard', 75.75, 99);

Addition (+) and Subtraction (-)

Calculate a discounted price.

SELECT name, price, price - 10.00 AS discounted_price FROM products;

Multiplication (*)

Calculate the total value of inventory for each product.

SELECT name, price * stock_quantity AS inventory_value FROM products;

Division (/) vs. Integer Division (DIV)

The / operator performs floating-point division, while DIV performs integer division.

-- Floating-point division
SELECT 10 / 4; -- Result: 2.5000

-- Integer division
SELECT 10 DIV 4; -- Result: 2

A practical example: finding out how many ‘packs of 10’ can be made from the current stock.

SELECT name, stock_quantity, stock_quantity DIV 10 AS packs_of_10 FROM products;

Modulus (%)

The modulus operator is useful for finding remainders. A common use case is determining if a number is even or odd.

SELECT 15 % 4; -- Result: 3 (because 15 = 3*4 + 3)

-- Find all products with an odd-numbered stock quantity
SELECT name, stock_quantity FROM products WHERE stock_quantity % 2 != 0;

Operator Precedence

MySQL follows a standard order of operations. Multiplication (*), division (/, DIV), and modulus (%) have higher precedence than addition (+) and subtraction (-).

Consider this query:

SELECT 10 + 5 * 2; -- Result: 20

In the query above, 5 * 2 is calculated first (resulting in 10), and then 10 + 10 is calculated. To override the default precedence, use parentheses ().

SELECT (10 + 5) * 2; -- Result: 30

Using parentheses, even when not strictly necessary, can make your calculations much easier to read and understand.