MySQL MOD()

Introduction to MySQL MOD()

MySQL MOD() function allows us to divide a literal number with another numeric expression to return the remainder of it. The MOD() takes numeric values as arguments for the function to execute and produce the result. The arguments can be said as dividend and divisor as in Maths which works on the fractional numeric values to output the precise remainder. In case, if the divisor or say the second parameters of the MySQL MOD() function is set to 0 (zero) then, the result will be NULL. This function can be used safely with BIGINT data type values but mostly it is used for fractional parts to perform the division and show the remainder after calculating logically.

Syntax

The following syntax code introduces the structure to apply the Math function MOD() in MySQL:

Syntax #1

MOD(a,b)

Syntax #2

a MOD b

Syntax #3

a % b

We have the above three syntaxes to use the MOD() function within the queries in MySQL.

• Here, the ‘a’ and ‘b’ parameters are the required numeric values that can be either fractional part or BIGINT data type values with the help of which the division process of MOD() function takes place. After the completion of the command query then the return value is the remainder that is balanced within numbers as in Maths calculation problems.
• Also, we can define the first argument value (a) as dividends and the other one-second argument (b) as the divisor for the MOD() calculation to produce the result of the division.
• Thus, with the literal numbers, the MOD() function returns the remains of the division and if the divisor is zero like MOD(dividend,0) then, the result will be NULL.

How MySQL MOD() Function work?

As per syntax above when we divide a numeric expression by another literal number then, the result will be the remainder for this MOD() function in MySQL.

This performs the same work as Modulus calculation is calculated in Maths that we do manually using logic and basic concepts. In addition, we also use SELECT query statement to supplement the MOD() function use in MySQL, so we use the MOD() function as:

SELECT MOD(a,b);

The working of MOD() function can be explained in detail with the help of some examples demonstrated below:

Code: Let us take a query using MOD() function for MySQL to illustrate the function syntaxes that can be executed in three forms-

SELECT MOD(152, 3);
Or,
SELECT 152 % 3;
Or,
SELECT 152 MOD 3;

Output:

Explanation: As you can see that the results show the remainder of each query form that is executed on the server. So, like this the Math function implements the logic to produce the modulus process result.

Examples to Implement MOD() function in MySQL

Let us illustrate the MySQLMOD() function with the help of following examples and show executions results generated simultaneously:

1. Some Simple Examples Using MOD() function in MySQL

Code #1

SELECT MOD(21,2);

Output:

It can be noticed that MySQL permits to use the modulus operator i.e. %, that can be considered as the synonym for this MOD() function to give the result also like this:

Code #2

SELECT 21 % 2;

Output:

Also, have a look of the results that are gained by using the MOD() function that accepts fractional elements to return the remainder of the division process. The query for this is written as follows:

Code #3

SELECT MOD(15.8, 3);

Output:

Again, using both dividend and divisor having fractional values, the result can be generated as shown below:

Code #4

SQL statement:
SELECT MOD(8.452, 2.14);

Output:

The result or remainder is also with fractional values. Also, view the below query with a divisor in fractional value only:

Code #5

SELECT 25 MOD 2.3;

Output:

2. Using MOD() function for zero divisor value

Suppose, we have set the divisor as zero and dividend like any literal numeric expression, then the MOD() function applied to it will produce the following result as a remainder:

Code #1

SQL Statement:
SELECT MOD(17,0);

Output:

You can view that the remainder of the above query with 0 divisor value is NULL when executed on the MySQL server.

Let us try out the result of MOD() function in MySQL when we set zero value for dividend and non-zero or say any literal number for divisor using the succeeding query:

Code #2

SELECT 0 % 5;

Output:

So, do remember that if we divide the 0 value with any numeric expression then, mathematically it will give the result zero, just opposite if we reverse the statement values when the divisor is zero and dividend is non-zero then the output will be NULL.

3. Using MOD() function on Database Table fields

Supposing, we have created a table named ‘Books’ where we will implement the MOD() function in MySQL. For this, the table creation code to be used is succeeding with fields BookID, BookName, Langauge and Price:

CREATE TABLE Orders(BookID INT PRIMARY KEY AUTO_INCREMENT, BookNameVarchar(255) NOT NULL, LanguageVarchar(255) NOT NULL, Price INT NOT NULL);

Let us enter some data records in the table Books with the following SQL query:

Code #1

INSERT INTO TABLE (BookID, BookName, Language, Price) VALUES(‘101’,’Algebraic Maths’,’English’,’2057’);

And so on.

Output: View the table here:

Now, we will useMySQL MOD() query on the table column ‘Price’ to check the values whether they are even or odd one:

Code #2

SELECT BookID, BookName, Language, Price,
IF (MOD(Price,2), 'Odd_Price','Even_Price') AS Odd_Even_Prices
FROM Books GROUP BY BookID ORDER BY BookID;

Output:

In this illustration:

• Here, the price’ column is used to fetch the values of it to perform the MOD() i.e. Numeric values.
• Again, when we add MOD() function, the Price column values will be divided by 2 as a divisor to retrieve the remainder as a result. The query will evaluate odd or even according to the results produced in zero or one.
• At last, we have applied the IF() function that when executed will show Odd and Even prices string on the basis of the result of the MOD() operation.

Conclusion

• The MOD() is a Math function provided in MySQL which performs an evaluation for the given values to generate the remnants after the division of the specified arguments.
• The MySQL MOD() thus implements a modulo operation on calling the function to provide the remainder of two numeric values passed in the function by division method.

Recommended Articles

This is a guide to MySQL MOD(). Here we discuss how does it work with examples to implement with proper outputs. You can also go through our other related articles to learn more –

1. MySQL Self Join
2. MySQL Timestamp
3. ANY in MySQL
4. MySQL Database Repair