SQL MOD Function: Syntax, Examples and Use Cases

By Dr. Zubair Khalid, DVM, MS, PhD · 2026-10-03

The MOD function in SQL returns the remainder left over after dividing one number by another. You write it as MOD(a, b) or with the % operator, and both forms give the same result in most database systems. It is the standard tool for testing divisibility, splitting rows into groups, and cycling through a repeating sequence of values.

Quick Answer

MOD(a, b) returns the remainder of a divided by b. MySQL and PostgreSQL support both MOD() and %. SQL Server supports only %, Oracle supports only MOD(), and SQLite has % plus a mod() function in builds with math functions enabled (3.35 and later).
A result of 0 means a divides evenly by b. That is how you find even numbers, multiples of 5, or every third row.
The result always has the same sign as the dividend a in the common implementations, so MOD(-7, 3) returns -1 in most systems.
Dividing by zero behaves differently by engine. PostgreSQL and SQL Server raise an error, MySQL and SQLite return NULL, and Oracle returns the dividend. Guard against it with a CASE expression or a NULLIF wrapper.
MOD works on integers and, in most systems, on decimals and numeric types too.

Syntax

The function form takes two arguments. The operator form takes the same two values with % between them.

MOD(dividend, divisor)
dividend % divisor

Argument	Required?	Meaning
`dividend`	Yes	The number being divided. This is the value whose remainder you want.
`divisor`	Yes	The number you divide by. Must not be zero.

Both arguments should be numeric. If either is NULL, the result is NULL.

How It Works

Modulo arithmetic answers a simple question: after taking out as many whole copies of the divisor as possible, what is left?

For two integers $a$ and $b$, the modulo result $r$ satisfies:

$$a = b \cdot q + r$$

where $q$ is the integer quotient and $r$ is the remainder. The remainder is always smaller in magnitude than the divisor.

Take MOD(17, 5). Five goes into 17 three times, which accounts for 15, and 2 is left over. So MOD(17, 5) = 2.

The most useful property is the zero case. When MOD(a, b) = 0, the divisor divides the dividend exactly. That single test powers most real-world uses of the mod function in SQL.

Sign behavior is worth knowing. In MySQL, PostgreSQL, SQLite, SQL Server and Oracle, the sign of the result follows the dividend. MOD(7, 3) is 1 and MOD(-7, 3) is -1. If you need a non-negative result regardless of sign, add the divisor and take the modulo again, or use ABS.

The operator and the function are interchangeable in MySQL and PostgreSQL. Oracle accepts only the MOD function and SQL Server accepts only the % operator, so check your platform if a query fails to parse.

Worked Example

The dataset is a 10-row employees table with an id, a name and a department column. The goal is to return only the employees whose id is even.

Input table:

id	name	department
1	Alice Chen	Sales
2	Bob Martinez	Engineering
3	Carol Nguyen	Marketing
4	David Okafor	Engineering
5	Eva Schmidt	Sales
6	Frank Rossi	Support
7	Grace Kim	Marketing
8	Hassan Ali	Engineering
9	Ivy Johnson	Support
10	Jack Brown	Sales

Query:

SELECT id, name, department
FROM employees
WHERE id % 2 = 0
ORDER BY id;

How the query runs:

FROM employees starts with the 10-row employee table.
WHERE id % 2 = 0 applies the modulo operator. It returns the remainder of id divided by 2. A remainder of 0 means the id is even, so only even-numbered employees pass the filter.
SELECT id, name, department returns the identifying columns for the matching rows.
ORDER BY id presents the even employees in ascending id order.

Result:

id	name	department
2	Bob Martinez	Engineering
4	David Okafor	Engineering
6	Frank Rossi	Support
8	Hassan Ali	Engineering
10	Jack Brown	Sales

The result was checked with an equivalent SQLite query.

More Examples

Every third row. Change the divisor to 3 and compare against a specific remainder. WHERE id % 3 = 1 returns ids 1, 4, 7 and 10. This is the basis of sampling every nth record.

Bucketing into groups. Modulo spreads rows across a fixed number of buckets. SELECT id, id % 4 AS bucket FROM employees assigns each row a bucket from 0 to 3. You can then aggregate per bucket to compare group sizes.

Testing divisibility by a business rule. To find order numbers that are multiples of 100, use WHERE order_id % 100 = 0. This pattern appears in batch processing and in splitting work across parallel jobs.

Combining with CASE. Modulo returns a number, so you often wrap it in a conditional to label rows. A CASE expression can turn the remainder into a readable tag, and the same technique works for any conditional logic you build with using CASE in SQL.

SELECT id, name,
       CASE WHEN id % 2 = 0 THEN 'even' ELSE 'odd' END AS parity
FROM employees
ORDER BY id;

Cycling through a repeating list. If you have a small lookup table of shift names, MOD(row_number, shift_count) picks a shift for each row in a round-robin pattern.

Working with string lengths. Modulo is not limited to id columns. You can apply it to any numeric expression, including the output of a length calculation. The SQL LENGTH function returns a count you can feed straight into MOD to group strings by length.

Guarding against zero. Wrap the divisor in NULLIF so a zero divisor produces NULL instead of an error.

SELECT id, MOD(id, NULLIF(id - 5, 0)) AS safe_mod
FROM employees;

Errors and How to Fix Them

Division by zero. A zero divisor raises an error in PostgreSQL and SQL Server, returns NULL in MySQL and SQLite, and returns the dividend in Oracle. Fix it by filtering out zero divisors or wrapping the divisor in NULLIF(divisor, 0).

Wrong result sign. If you expect a positive remainder but get a negative one, the dividend is negative. Add the divisor to shift the result into the positive range, or apply ABS if the sign does not matter to your logic.

Type mismatch. Passing a text value where a number is expected causes a conversion error or an implicit cast that surprises you. Cast explicitly with CAST(value AS INTEGER) when the source column is text.

Operator not supported. A few dialects do not accept % and require the MOD function instead. If your query fails to parse, switch to the function form.

NULL propagation. If either argument is NULL, the whole expression is NULL, and a WHERE clause that compares against it will filter the row out. Use COALESCE on the input if you need a default, as described in SQL COALESCE Function: Syntax and Examples.

Common Mistakes

Confusing modulo with integer division. 17 / 5 gives 3 in integer arithmetic, while MOD(17, 5) gives 2. They answer different questions. Use the right one for the job.
Assuming the result is always positive. The sign follows the dividend in the common implementations. Test with negative values before you rely on the output.
Forgetting the zero divisor case. A divisor that comes from a column can be zero for some rows. Guard it or your query fails at runtime.
Using modulo for random sampling without a stable key. If the column you apply modulo to changes between runs, your sample changes too. Pick a stable key such as a primary key.
Comparing a modulo result to a float. MOD(a, b) = 0.0 can behave unexpectedly with floating-point values. Compare against an integer zero when the inputs are integers.
Expecting the same bucket count as the divisor. MOD(x, 4) produces values 0, 1, 2 and 3, which is four buckets. Off-by-one errors here are common when you size arrays or partitions.

Limitations

Modulo tells you about divisibility and remainders, nothing more. It cannot rank rows, detect duplicates, or measure how far apart two values are. For those tasks you need window functions or comparison operators.

The function also behaves differently across numeric types. With floating-point inputs, the result carries the same precision issues as any float arithmetic, so exact equality tests against zero can fail. Stick to integer columns when the zero test matters. And because the sign convention follows the dividend, code that assumes a non-negative remainder will break on negative inputs unless you normalize the result yourself.

Frequently Asked Questions

What is the difference between MOD and the % operator in SQL?

They compute the same remainder. MOD(a, b) and a % b are equivalent in MySQL and PostgreSQL. SQL Server supports only % and Oracle supports only MOD. The function form reads more clearly in long expressions, while the operator form is shorter. Some dialects support only one of the two, so check your platform.

How do I find even or odd rows in SQL?

Test the row key against 2. WHERE id % 2 = 0 returns even rows and WHERE id % 2 = 1 returns odd rows. This works on any integer column, not just a primary key. If the column can be negative, remember that the sign follows the dividend.

What happens if I divide by zero with MOD?

It depends on the database. PostgreSQL and SQL Server raise an error, MySQL and SQLite return NULL, and Oracle returns the dividend. Wrap the divisor in NULLIF(divisor, 0) to convert the zero case into a NULL result, or filter those rows out before the modulo runs.

Can I use MOD on decimal or float values?

Yes in most systems, but the result inherits floating-point precision. MOD(5.5, 2) returns 1.5. Avoid exact equality tests against zero with float inputs because rounding can make a value that should be zero come out slightly off. Use integer columns when the zero test drives your logic.

How do I split rows into N equal groups with MOD?

Apply modulo to a stable numeric key and use the remainder as the group number. id % 4 produces four groups labeled 0 through 3. The groups will not be exactly equal in size unless the row count is a multiple of N, so check the counts before you treat them as balanced.

References

This article draws on the standard references listed under Further Reading.