MOD()

Syntax

How it works

MOD(a, b) returns the remainder of a divided by b. For example MOD(17, 5) is 2, because 5 goes into 17 three times with 2 left over. When the divisor evenly divides the dividend the remainder is 0, which is exactly what makes MOD the go-to test for divisibility.

The % operator is an equivalent shorthand in MySQL and SQL Server, so a % b means the same thing as MOD(a, b). PostgreSQL supports both MOD() and % as well. SQLite is the odd one out: it offers the % operator but does not ship a MOD() function, so on SQLite you write a % b.

Three classic uses cover most real work. Even or odd detection with MOD(n, 2) returns 0 for even numbers and 1 for odd. Sampling every Nth row uses MOD(id, n) = 0 against a sequential id. Cyclic buckets use MOD(value, k) to wrap numbers into the range 0 to k minus 1, which is handy for round robin assignment and simple hashing. See ABS() when you need to normalise the sign of a result, and CASE to turn a remainder into a readable label.

One detail worth remembering: when the dividend is negative, the sign of the remainder follows the dividend in most engines, so MOD(-7, 3) is -1 rather than 2. This matters whenever a MOD result can go negative and you were expecting a value in the range 0 to b minus 1.

Examples

Label rows even or odd with CASE

-- MOD(n, 2) is 0 for even numbers and 1 for odd
SELECT id,
       CASE WHEN MOD(id, 2) = 0 THEN 'even'
            ELSE 'odd'
       END AS parity
FROM orders
ORDER BY id;

id | parity
---+-------
 1 | odd
 2 | even
 3 | odd
 4 | even

Pick every 3rd row

-- keep only ids that divide evenly by 3
SELECT id, customer
FROM orders
WHERE MOD(id, 3) = 0
ORDER BY id;

id | customer
---+---------
 3 | Sara
 6 | Omar
 9 | Lina

Wrap a value into a fixed range

-- map any hour count onto a 24 hour clock (0..23)
SELECT total_hours,
       MOD(total_hours, 24) AS hour_of_day
FROM shifts;

total_hours | hour_of_day
------------+------------
         10 |         10
         27 |          3
         50 |          2

The % operator is equivalent (MySQL, SQL Server)

-- a % b returns the same remainder as MOD(a, b)
SELECT MOD(17, 5) AS with_mod,
       17 % 5      AS with_operator;

with_mod | with_operator
---------+--------------
       2 |            2

Assign rows to round robin buckets

-- spread ids across 3 workers: buckets 0, 1, 2
SELECT id,
       MOD(id, 3) AS worker
FROM tasks
ORDER BY id;

id | worker
---+-------
 1 |      1
 2 |      2
 3 |      0
 4 |      1

Common mistakes

-- Divisor of 0: error in some engines, NULL in others
SELECT MOD(10, 0) FROM dual;

-- Guard the divisor so it is never 0
SELECT CASE WHEN divisor <> 0
            THEN MOD(10, divisor)
            ELSE NULL
       END AS safe_mod
FROM settings;

-- Expecting 2, but negatives surprise you
SELECT MOD(-7, 3) AS r;   -- returns -1, not 2

-- Force a non-negative result in the range 0..b-1
SELECT MOD(MOD(-7, 3) + 3, 3) AS r;   -- returns 2

-- MOD() does not exist in SQLite
SELECT MOD(id, 2) FROM orders;

-- Use the % operator, which SQLite does support
SELECT id % 2 FROM orders;

Performance

MOD() is a cheap arithmetic operation on a single row, so the function itself is never the bottleneck. The cost comes from where you put it. A filter like WHERE MOD(id, 3) = 0 wraps the column in a function, which usually prevents the optimiser from using an index on id and forces a full scan.

If you sample rows this way on a large table often, consider storing the bucket as a computed or persisted column and indexing it, so the engine can seek the matching rows instead of computing MOD for every row. On MySQL a generated column plus an index, or on PostgreSQL an expression index such as CREATE INDEX ON orders ((id % 3)), both let the filter use an index directly.

For even or odd style splits the data is roughly half in each bucket, so an index rarely helps there anyway; a scan is expected. Reserve the index tricks for selective cases like every Nth row where the matching set is small.

Interview questions

SELECT n,
       CASE WHEN MOD(n, 2) = 0 THEN 'even' ELSE 'odd' END AS parity
FROM numbers;