EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL MOD()
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL MOD()

By Aanchal SharmaAanchal Sharma

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:

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax #1

MOD(a,b)

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,768 ratings)

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:

MySQL MOD() - 1

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:

MySQL MOD() - 2

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:

MySQL MOD() - 3

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:

MySQL MOD() - 4

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:

dividend and divisor

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:

fractional values

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:

divisor as zero

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:

non-zero

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:

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:

column ‘Price

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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more