MOD and the % Operator in SQL: Remainders in Practice

MOD(a, b) returns the remainder of dividing a by b — the same value as the % operator. It is the basic tool for testing parity, picking every N-th row, evenly sharding by an identifier, and any cyclic logic.

Basic behavior

MOD(a, b) and a % b produce the same result: the remainder left after integer division. The sign of the result matters, and we will come back to it below.

SELECT
  MOD(10, 3)  AS a,   -- 1
  10 % 3      AS b,   -- 1
  MOD(9, 3)   AS c,   -- 0
  MOD(7, 10)  AS d;   -- 7

Key points:

• MOD(a, b) and a % b are interchangeable in PostgreSQL and MySQL — pick whichever reads clearer.
• If a divides evenly by b, the remainder is 0. That is your divisibility test.
• A divisor of 0 is an error: MOD(5, 0) throws division by zero, just like plain division.

Even/odd and every N-th row

The remainder by 2 instantly splits rows into even and odd. Tag orders by the parity of their id:

SELECT
  id,
  amount,
  CASE WHEN MOD(id, 2) = 0 THEN 'even' ELSE 'odd' END AS parity
FROM orders
ORDER BY id;

The same trick scales to "every N-th row". To take roughly every tenth user for a spot check, filter on the remainder:

SELECT id, email, country
FROM users
WHERE MOD(id, 10) = 0
ORDER BY id;

Gotcha. MOD(id, 10) = 0 only works as sampling if the id values are dense and evenly spaced. On a sequential id it is reliable, but on UUIDs or sequences with large gaps the sample skews. For fair sampling on text, take the remainder of a hash, not of the raw id.

Sharding by id % bucket

The remainder is the natural way to spread rows across a fixed number of buckets. Distribute users across 4 shards by their id:

SELECT
  MOD(id, 4) AS shard,
  COUNT(*)   AS users
FROM users
GROUP BY MOD(id, 4)
ORDER BY shard;

Each user lands in the same shard 0..3 every time, which is handy for batch processing or rolling out features by cohort:

SELECT id, email
FROM users
WHERE MOD(id, 4) = 2   -- process only shard 2 in this batch
ORDER BY id;

This works because for non-negative id the result of MOD(id, 4) always falls in 0..3. Negative values behave differently — more on that next.

The sign rule and negatives

The main MOD trap: the sign of the result follows the sign of the dividend (the first argument), not the divisor. This is the SQL-standard behavior in PostgreSQL and MySQL.

SELECT
  MOD(-10, 3)  AS a,   -- -1
  MOD(10, -3)  AS b,   -- 1
  MOD(-10, -3) AS c;   -- -1

Because of this, MOD(value, n) = 0 for parity and divisibility is safe, but MOD(value, n) as a bucket index is not: for a negative value it yields a negative bucket. To always get a non-negative remainder, add the divisor and take the remainder once more:

-- always lands in 0..(n-1), even for negative input
SELECT MOD(MOD(-10, 3) + 3, 3) AS bucket;  -- 2

Remember NULL too: if either argument is NULL, the result is NULL, not 0. Account for that in WHERE and GROUP BY.

Database differences

• PostgreSQL and MySQL: both support MOD(a, b) and a % b with the same sign rule (by the dividend).
• PostgreSQL: % works on integers and numeric. For fractional types there is MOD(numeric, numeric), for example MOD(5.5, 2) is 1.5.
• ClickHouse: use modulo(a, b) or the % operator; there is also moduloOrZero(a, b) that returns 0 instead of erroring on a zero divisor, and positiveModulo(a, b) for an always non-negative result.

-- ClickHouse: safe modulo and always-positive modulo
SELECT
  moduloOrZero(10, 0)     AS a,   -- 0, no error
  positiveModulo(-10, 3)  AS b;   -- 2