# SQL MOD Function: Syntax, Examples and Use Cases

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.

```sql
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:

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

How the query runs:

1. `FROM employees` starts with the 10-row employee table.
2. `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.
3. `SELECT id, name, department` returns the identifying columns for the matching rows.
4. `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](/blog/data-analysis/using-case-in-sql).

```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](/blog/data-analysis/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.

```sql
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](/blog/data-analysis/sql-coalesce-function-syntax-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.

## Further Reading

- [Codd EF (1970). A relational model of data for large shared data banks. Communications of the ACM](https://doi.org/10.1145/362384.362685)
- [PostgreSQL Documentation: SELECT](https://www.postgresql.org/docs/current/sql-select.html)
- [SQLite: SQL As Understood By SQLite](https://www.sqlite.org/lang.html)
- [Wilson G, Bryan J, Cranston K et al. (2017). Good enough practices in scientific computing. PLOS Computational Biology](https://doi.org/10.1371/journal.pcbi.1005510)
- [PostgreSQL Tutorial: The SQL Language](https://www.postgresql.org/docs/current/tutorial-sql.html)
- [SQLite: Built-In Scalar SQL Functions](https://www.sqlite.org/lang_corefunc.html)

## Related Articles

- [SQL RIGHT Function: Syntax, Examples and Use Cases](/blog/data-analysis/sql-right-function-syntax-examples)
- [SQL Alias: Syntax and Examples for Tables and Columns](/blog/data-analysis/sql-alias)
- [SQL DECODE Function: Syntax and Examples](/blog/data-analysis/sql-decode-function-syntax-examples)
- [SQL REPLACE Function: Syntax and Examples](/blog/data-analysis/sql-replace-function)
- [SQL LENGTH Function: Syntax and Examples for String Length](/blog/data-analysis/sql-length-function)