MOD()
SQL · Math Functions
Theory
MOD returns the remainder left over after dividing one whole number by another. MOD(10, 3) is 1 because 10 = 3 × 3 + 1, MOD(12, 4) is 0 because 4 divides 12 exactly, and MOD(id, 2) is 0 for even ids and 1 for odd ids. Many databases also write it as the % operator: 10 % 3.
Remainders are surprisingly useful in SQL. MOD(id, 2) splits rows into even and odd; MOD(id, 3) deals rows into three repeatable buckets for an A/B/C test or for spreading work across three batch jobs; WHERE MOD(id, 10) = 0 keeps every tenth row as a quick, stable sample; and MOD(year, 4) = 0 is the first step of a leap-year check. Spellings differ: PostgreSQL, MySQL and Snowflake support both MOD() and %, Oracle has MOD() only, SQL Server has % only, and SQLite always supports %. With negative numbers the sign of the result usually follows the dividend, so -7 % 3 is -1, not 2.
Why we use it
Remainders give a simple, repeatable way to split, cycle or sample rows, and to test divisibility. Unlike RANDOM, the same id always lands in the same bucket.
When to use it
Use MOD for even/odd checks, round-robin buckets, every-Nth-row samples and divisibility rules. Use RANDOM when you want a fresh random sample each time, and FLOOR(x / n) when you need the quotient rather than the remainder.
How it works
- Dividend and divisor, e.g. (10, 3)
- Is the divisor zero? Yes: Error or NULL — division by zero. No: Continue.
- Divide and drop the whole-number part of the result
- Return the remainder (1)
- Use it to bucket, filter or test divisibility
Syntax
MOD(dividend, divisor)Example
SELECT MOD(10, 3);
-- 1Key points
- SQLite/most dialects also support the % operator directly
- Common for splitting rows into buckets, e.g. MOD(id, 2) for odd/even
- SQL Server has only %, Oracle has only MOD()
- Sign follows the dividend in most databases: -7 % 3 = -1
- MOD(id, 10) = 0 is a stable, repeatable 10% sample