MOD() Function in SQL
Introduction
MOD is a numeric SQL operation that returns the remainder left after one number is divided by another. If 17 is divided by 5, three complete groups of five consume 15 and two remain. Therefore, MOD(17, 5) returns 2. The first argument is the dividend, the second is the divisor, and the output is the remainder.
SELECT MOD(17, 5) AS remainder;
-- 17 = (5 * 3) + 2
-- Result: 2
Remainder arithmetic supports divisibility checks, even and odd classification, packaging leftovers, partial batches, cyclic numbering, round-robin assignment, sampling patterns, and pagination analysis. Reliable SQL also requires attention to zero divisors, NULL, negative operands, decimal types, product-specific syntax, indexing, and the difference between a programming-language remainder and a normalized mathematical modulo.
Syntax and Terminology
The function form accepts a dividend and divisor. Several databases also provide a percent operator.
MOD(dividend, divisor)
dividend % divisor
For 25 divided by 4, the quotient is 6 and the remainder is 1 because 25 equals 4 times 6 plus 1.
SELECT MOD(25, 4) AS remainder;
-- Result: 1
Function and operator availability varies by DBMS. Use the syntax supported by the deployment target rather than assuming both forms are portable.
The Division Identity
Remainder calculations are connected to the identity dividend = divisor * quotient + remainder. For positive integers, the quotient used for complete groups is the lower whole-number quotient, and the remainder lies from zero through one less than the divisor.
17 = (5 * 3) + 2
This identity is useful for tests and reconciliation. Negative operands and decimal types require the quotient rule documented by the DBMS, so do not apply the positive-integer range assumption blindly.
Basic Remainder Examples
When division is exact, the remainder is zero. When the positive dividend is smaller than the positive divisor, the quotient has no complete group and the dividend itself remains.
SELECT MOD(20, 6) AS first_value, -- 2
MOD(20, 5) AS exact_value, -- 0
MOD(3, 10) AS small_value, -- 3
MOD(0, 5) AS zero_value; -- 0
These cases establish the essential behavior before columns, expressions, signs, and database differences are introduced.
Testing Divisibility
An integer is exactly divisible by a nonzero integer divisor when its remainder is zero.
SELECT value
FROM numbers
WHERE MOD(value, 5) = 0;
This condition finds values divisible by five. It does not return the quotient or explain how many groups were formed. It answers only whether anything remains.
Finding Even Numbers
An integer is even when division by two leaves no remainder.
SELECT value
FROM numbers
WHERE MOD(value, 2) = 0;
The zero-remainder rule is valid for positive, negative, and zero integer values under ordinary remainder semantics. Zero is even because it is exactly divisible by two.
Finding Odd Numbers
An integer is odd when it is not divisible by two. Use a nonzero remainder test when negative values may occur.
SELECT value
FROM numbers
WHERE MOD(value, 2) <> 0;
Checking for remainder exactly 1 can fail on systems that return -1 for a negative odd dividend. The nonzero test expresses the actual property without depending on the sign convention.
Using MOD with Columns and Expressions
Both arguments can be columns or calculated expressions.
SELECT number_value,
MOD(number_value, 2) AS parity_remainder,
MOD(number_value + adjustment, divisor) AS adjusted_remainder
FROM number_sample;
Validate the divisor before evaluation. An expression can become zero even when its source columns are individually nonzero.
Complete Groups and Leftovers
FLOOR and MOD naturally describe positive integer grouping. FLOOR returns the number of complete groups, while MOD returns the units that remain after those groups.
SELECT FLOOR(53.0 / 10) AS full_boxes,
MOD(53, 10) AS remaining_items;
-- Results: 5 full boxes and 3 remaining items
The two results reconcile because five times ten plus three equals 53. If the question is how many boxes are required for every item, CEIL returns six instead.
Packaging and Inventory
A warehouse can report full cases and loose units together.
SELECT product_id,
quantity,
units_per_case,
FLOOR(quantity * 1.0 / NULLIF(units_per_case, 0))
AS full_cases,
MOD(quantity, NULLIF(units_per_case, 0))
AS loose_units
FROM inventory;
NULLIF prevents an attempted zero-divisor calculation in many systems. A NULL result should trigger data-quality handling because an invalid case size is not equivalent to zero loose units.
Teams, Rows, and Seats
For 37 students and teams of five, MOD returns two students outside the seven complete teams. For 103 people and rows of 20 seats, it returns three people in the partial final row.
SELECT MOD(37, 5) AS students_remaining,
MOD(103, 20) AS people_in_partial_row;
If the remainder is zero, there is no partial group. If it is positive for a nonnegative quantity, one additional group is needed to include all units.
Batch Processing
Suppose 4,501 records are processed in batches of 1,000. Four full batches contain 4,000 records and 501 remain for a partial fifth batch.
SELECT FLOOR(4501.0 / 1000) AS full_batches,
MOD(4501, 1000) AS final_batch_records,
CEIL(4501.0 / 1000) AS batches_required;
Keep full, partial, required, scheduled, and completed batch counts distinct. MOD describes arithmetic remainder, not execution status.
Pagination Remainders
For 103 records and page size 20, MOD returns three records on the partial final page. CEIL returns six total pages.
SELECT MOD(total_records, NULLIF(page_size, 0))
AS records_on_partial_page,
CEIL(total_records * 1.0 / NULLIF(page_size, 0))
AS total_pages
FROM pagination_summary;
A remainder of zero means the final page is full, not empty. If total records is zero, product requirements must decide whether the interface reports zero pages or displays one empty page.
Cyclic Numbering
Remainders repeat in a predictable cycle. For a positive divisor four, integer inputs produce repeating remainder classes 0, 1, 2, and 3.
SELECT sequence_number,
MOD(sequence_number, 4) AS cycle_position
FROM sequence_data;
This pattern is useful for labels, rotating work, test partitioning, and controlled grouping. It is deterministic only when the sequence value is stable.
One-Based Round-Robin Assignment
Raw remainder classes begin at zero. To assign positive sequence numbers across teams 1 through n, subtract one before MOD and add one afterward.
SELECT employee_id,
MOD(employee_id - 1, 4) + 1 AS team_number
FROM employees;
Employee IDs 1 through 4 map to teams 1 through 4, and employee 5 returns to team 1. This assumes positive, gap-tolerant identifiers and does not guarantee balanced active membership when rows are deleted or filtered.
Stable Row Numbers Versus IDs
Modulo assignment based on an ID reflects the ID values, not current row position. Gaps can create uneven groups, and unrelated ID allocation rules may bias results. When assignment should follow a sorted current set, calculate a stable ROW_NUMBER first and apply MOD to that number.
WITH ordered AS (
SELECT employee_id,
ROW_NUMBER() OVER (ORDER BY employee_id) AS row_num
FROM employees
WHERE active_flag = 1
)
SELECT employee_id,
MOD(row_num - 1, 4) + 1 AS team_number
FROM ordered;
Specify deterministic ordering. Ties or changing data can alter row numbers and assignments between executions.
Every Nth Record
A remainder predicate can select values divisible by n.
SELECT record_id
FROM records
WHERE MOD(record_id, 10) = 0;
This selects IDs such as 10, 20, and 30. It is not statistically random sampling when IDs contain time, source, or business patterns. Use an approved sampling design for analytics and experiments.
Sampling Bias and Experiment Assignment
Sequential IDs frequently encode creation order. Selecting every tenth ID can overrepresent a periodic import job, location, customer type, or time of day. Gaps from failed transactions and deletions can add further distortion. The result is deterministic systematic sampling, not automatically a representative random sample.
For experiments, hashing a stable subject key before bucket reduction is usually better than applying MOD directly to a sequential identifier. The hash function, seed or salt, bucket count, eligibility rule, and experiment version must remain stable so one subject receives the same treatment on repeated visits.
-- Conceptual only: hash function syntax varies
normalized_bucket(hash_function(subject_id), 100)
AS experiment_bucket
Never use a database's unspecified row order or a hash whose algorithm can change across versions. Monitor group balance and important business attributes. A deterministic bucket assignment supports reproducibility but does not by itself prove that an experiment is unbiased.
MOD Is Not a Security Boundary
Remainder classes are predictable. They must not be used to hide identifiers, create access tokens, generate passwords, or authorize users. If an attacker knows the divisor and key pattern, the assigned bucket is trivial to calculate.
Use cryptographically appropriate libraries and an authorization system for security controls. MOD may participate after a secure hash in a nonsecurity distribution design, but reducing a hash to a small bucket necessarily discards information and does not make the bucket secret.
Server and Partition Assignment
A simple expression can map a numeric key to a fixed number of buckets.
SELECT request_id,
MOD(request_id, 4) AS server_bucket
FROM request_queue;
Simple modulo partitioning has an important limitation: changing the bucket count can move most keys. It also performs poorly with skewed IDs and cannot account for server capacity. Production distributed systems often need consistent hashing, replication, health checks, and rebalancing instead.
Negative Operands
Negative remainder behavior must be verified on the target DBMS. Many implementations return a remainder whose sign follows the dividend or define the operation through a quotient truncated toward zero. A mathematical modulo operation often expects a nonnegative result for a positive divisor, so the two concepts are not universally identical.
SELECT MOD(-7, 2) AS negative_dividend,
MOD(7, -2) AS negative_divisor;
Do not hard-code expected signs until product documentation and tests confirm them. Even checks remain based on zero; odd checks should use nonzero.
Normalizing a Cycle Position
When a nonnegative result from zero through n minus one is required and n is positive, a common normalization pattern applies the remainder operation twice.
MOD(MOD(value, cycle_size) + cycle_size, cycle_size)
The inner result is shifted by one positive cycle before the outer operation normalizes it. Verify syntax, types, overflow, and negative semantics on the DBMS. Reject zero or negative cycle sizes if the domain requires a positive cycle.
Decimal Remainders
Some databases support remainder operations on exact decimal values. Conceptually, MOD(10.5, 3) is 1.5 because 10.5 equals three times 3 plus 1.5.
SELECT MOD(10.5, 3) AS decimal_remainder;
Accepted types, precision, scale, and sign behavior differ. Use exact decimals where exact base-10 behavior matters. Floating-point inputs may produce small approximation artifacts near expected boundaries.
Modular Equivalence Classes
Two integers belong to the same remainder class for a positive divisor n when their difference is divisible by n. For divisor four, values 1, 5, 9, and 13 all occupy the same normalized class because each is separated from the next by four. This concept explains why cyclic labels repeat and why a remainder can act as a compact bucket identifier.
SELECT value,
MOD(value, 4) AS remainder_class
FROM number_sample
ORDER BY value;
For nonnegative values, class labels commonly range from zero through n minus one. With negative values, normalize the result if the application requires that range and verify the target DBMS behavior. A class identifies a repeating position; it does not retain the original number or show how many complete cycles occurred.
Reconstructing a Positive Integer Quantity
For a nonnegative integer dividend and positive integer divisor, the quotient and remainder should reconstruct the source exactly. This gives production systems a useful validation rule.
WITH grouped AS (
SELECT quantity,
capacity,
FLOOR(quantity * 1.0 / capacity) AS full_groups,
MOD(quantity, capacity) AS remaining_units
FROM allocation_input
WHERE quantity >= 0
AND capacity > 0
)
SELECT quantity,
capacity,
full_groups,
remaining_units,
full_groups * capacity + remaining_units
AS reconstructed_quantity
FROM grouped;
If reconstruction differs, investigate implicit conversion, approximate types, inconsistent units, overflow, or an incorrect quotient rule. This check is especially valuable in ETL and reconciliation pipelines.
Periodic Events and Time Slots
MOD can label repeating time slots after time has been converted into a stable integer sequence. For example, a process that repeats every six intervals can map interval numbers to positions zero through five.
SELECT interval_id,
MOD(interval_number, 6) AS cycle_position
FROM scheduled_interval;
Do not apply this pattern directly to loosely defined calendar units. Months have different lengths, daylight-saving transitions can change local-hour sequences, and missing intervals can disrupt assumptions. Generate the sequence in a documented time zone and define whether the first interval maps to zero or one.
Weekday Rotation
A seven-position business rotation can use one-based normalization.
SELECT work_date,
MOD(day_sequence - 1, 7) + 1 AS rotation_day
FROM work_calendar;
The sequence should come from an approved calendar table rather than simple date subtraction when holidays, nonworking days, or regional schedules matter. MOD repeats the position but does not know which dates the business excludes.
Deterministic Data Striping
Test suites and batch jobs sometimes divide a stable set into parallel stripes. A numeric, stable key can assign each row to one of n workers.
SELECT test_case_id,
MOD(test_case_id, :worker_count) AS worker_index
FROM test_case
WHERE active_flag = 1;
This is easy to reproduce, but balance depends on key distribution and workload cost. Equal row counts do not guarantee equal execution time. Changing worker count reshuffles assignments, so persisted checkpoints and retry logic must account for the exact worker-count version used.
Hash Values and Nonnegative Buckets
Applications sometimes calculate a hash and then use a remainder to select a bucket. Hashes can be negative, and taking ABS first is not universally safe because the minimum signed integer may overflow. Normalize the remainder under tested DBMS semantics instead.
-- Conceptual normalized bucket for positive bucket_count
MOD(
MOD(hash_value, bucket_count) + bucket_count,
bucket_count
) AS bucket_number
Use a hash function and data type designed for the workload. Collision behavior, security requirements, partition stability, and cross-language compatibility need separate analysis; MOD only reduces a value into a fixed range.
Changing the Bucket Count
Modulo assignment depends directly on the divisor. Moving from four buckets to five changes the result for many keys, which can cause large data movement, cache misses, duplicate processing, or missed checkpoints.
Plan rebucketing as a versioned migration. Record old and new bucket counts, run dual assignment when needed, reconcile item totals, and switch readers only after the destination is complete. Distributed systems with frequently changing membership usually need a more stable partitioning method than simple key modulo.
Concurrency and Persisted Assignment
A query can calculate a cycle position deterministically while still producing unstable operational ownership. If active rows are resequenced during concurrent inserts, worker assignments can change between selection and processing. Two workers may process the same row or neither may retain it.
Persist assignment in a transaction when ownership matters, and enforce an appropriate uniqueness or status rule. Include assignment version, bucket count, worker identifier, and timestamp. MOD calculates a candidate bucket; it does not lock work, coordinate retries, or guarantee exactly-once execution.
Type Conversion and Overflow
The multiplication used to reconstruct a dividend, the subtraction used for one-based cycles, and arithmetic inside a divisor expression can overflow before MOD executes. Return types and implicit conversions vary by DBMS.
SELECT MOD(
CAST(large_value AS DECIMAL(38, 0)),
CAST(group_size AS DECIMAL(38, 0))
) AS safe_range_remainder
FROM large_calculation;
Choose types that safely represent every intermediate value, not only the expected remainder. Test minimum and maximum values through the application driver because parameter typing can change SQL expression behavior.
Remainder Constraints
A remainder can support validation when the business rule requires quantities in fixed increments. For example, an order may need to contain complete packs of six.
-- Conceptual check constraint; syntax varies
CHECK (quantity >= 0 AND MOD(quantity, 6) = 0)
Use this only when the increment is a durable data invariant. If package size varies by product or changes over time, a static constraint cannot reference the correct external rule in many databases. Validate through a controlled service or trigger only after reviewing consistency and maintenance costs.
Observability for Remainder-Based Workflows
Monitor rows per bucket, processing time per bucket, remainder distributions, zero-divisor rejections, NULL capacities, partial-group frequency, rebucketing movement, retry counts, and reconstruction failures. An even distribution of bucket numbers can still hide a heavy concentration of expensive work.
Operational metrics should include the divisor and rule version. Without them, a dashboard cannot explain why distributions changed after a deployment.
Zero Dividend, Zero Divisor, and NULL
A zero dividend with a nonzero divisor has remainder zero. A zero divisor is undefined and may raise an error, return NULL, or behave differently by DBMS. NULL in either argument normally produces NULL.
SELECT MOD(0, 5); -- commonly 0
SELECT MOD(NULL, 5); -- normally NULL
SELECT MOD(10, NULL); -- normally NULL
SELECT MOD(10, NULLIF(divisor, 0)) -- defensive pattern
FROM calculation_input;
NULLIF prevents an attempted zero division in common implementations, but the application should still validate and report invalid divisors explicitly.
COALESCE and Missing Values
COALESCE can replace a NULL dividend, but it changes unknown data into a chosen value.
SELECT MOD(COALESCE(quantity, 0), 10) AS remainder
FROM inventory;
Use this only when missing quantity is defined as zero. Never replace a missing divisor with zero, and do not replace it with an arbitrary positive capacity merely to avoid an error.
MOD Versus FLOOR and CEIL
For 17 items and capacity five, FLOOR returns three complete groups, MOD returns two leftover items, and CEIL returns four groups required for all items.
| Operation | Result | Question answered |
|---|---|---|
FLOOR(17.0 / 5) | 3 | How many complete groups? |
MOD(17, 5) | 2 | How many units remain? |
CEIL(17.0 / 5) | 4 | How many groups hold everything? |
ROUND is unrelated: it chooses a value according to a precision rule rather than returning a quotient or remainder.
Database Syntax and Portability
Remainder syntax is less uniform than many SQL functions.
| Database | Common syntax | Review point |
|---|---|---|
| MySQL | MOD(a,b) or a % b | Types and sign behavior |
| PostgreSQL | a % b | Operator support by numeric type |
| SQL Server | a % b | Conversions and result type |
| Oracle | MOD(a,b) | Number behavior and negative values |
Portable designs test function spelling, operator availability, integer and decimal inputs, negative operands, zero divisors, NULL, return types, overflow, and driver metadata.
MOD in WHERE Predicates
A predicate such as MOD(id, 10) = 0 applies a calculation to the column and may prevent a conventional index seek. Unlike a bounded tolerance condition, divisibility corresponds to many separated values, so it cannot usually be rewritten as one continuous range.
For frequent stable queries, a database may support an expression index, filtered structure, partitioning strategy, or generated remainder column. Measure read benefit against write and storage cost.
Generated Cycle Columns
-- Conceptual syntax; verify target DBMS support
ALTER TABLE work_item
ADD bucket_number INTEGER
GENERATED ALWAYS AS (MOD(item_id, 16));
CREATE INDEX ix_work_item_bucket
ON work_item(bucket_number);
A stored bucket becomes a schema contract. Changing the bucket count or assignment policy may require migration, so use this only for a stable domain decision.
Worked Manufacturing Example
A plant produces 1,257 units in lots of 50. Twenty-five full lots consume 1,250 units and seven remain.
SELECT production_run_id,
FLOOR(produced_units * 1.0 / lot_size) AS full_lots,
MOD(produced_units, lot_size) AS partial_lot_units,
CASE WHEN MOD(produced_units, lot_size) = 0
THEN 0 ELSE 1 END AS partial_lot_count
FROM production_run;
Validate that quantities are nonnegative and lot size is positive. The partial-lot indicator describes arithmetic allocation, not whether that lot has started or completed.
Worked Rotation Example
A support schedule rotates tickets across three queues. A stable sequence number, not an arbitrary changing row order, drives assignment.
WITH sequenced AS (
SELECT ticket_id,
ROW_NUMBER() OVER (
ORDER BY created_at, ticket_id
) AS sequence_number
FROM ticket
WHERE assignment_status = 'PENDING'
)
SELECT ticket_id,
MOD(sequence_number - 1, 3) + 1 AS queue_number
FROM sequenced;
Concurrent inserts or reruns can change assignments unless they are persisted transactionally. MOD supplies the cycle but not concurrency control or workload balancing.
Auditing and Reconciliation
For packaging and batch rules, retain dividend, divisor, quotient, remainder, units, rule version, and calculation time. A useful validation for nonnegative integers confirms that quotient times divisor plus remainder equals the dividend.
SELECT calculation_id,
total_quantity,
group_size,
FLOOR(total_quantity * 1.0 / group_size) AS full_groups,
MOD(total_quantity, group_size) AS remainder
FROM allocation_audit;
Record manual overrides separately. Changing the remainder to force a desired report breaks the arithmetic relationship and hides data problems.
Testing Strategy
Test exact division, a nonzero remainder, dividend smaller than divisor, zero dividend, NULL arguments, zero divisor, positive and negative operands, positive and negative divisors, exact decimals, approximate values, maximum magnitudes, expression typing, and every supported syntax form.
Business tests should verify parity, divisibility, quotient-remainder reconciliation, cyclic boundaries, one-based mapping, page leftovers, packaging, deterministic ordering, and assignment behavior when rows are inserted or removed. Run the tests on each supported DBMS through production drivers.
Property-Based Verification
Example tests catch known cases, while property-based tests generate many valid input combinations and verify general rules. For nonnegative integer dividend a and positive integer divisor b, calculate quotient q and remainder r, then assert that a = b * q + r, r >= 0, and r < b.
-- Validation query for a positive-integer test set
SELECT test_id
FROM remainder_test
WHERE dividend <>
divisor * FLOOR(dividend * 1.0 / divisor)
+ MOD(dividend, divisor)
OR MOD(dividend, divisor) < 0
OR MOD(dividend, divisor) >= divisor;
The query should return no rows under those stated assumptions. Create separate properties for negative and decimal inputs based on documented DBMS semantics. Include values near type limits to expose overflow in intermediate multiplication or subtraction.
Common Mistakes
Frequent mistakes include confusing remainder with quotient, using MOD when the requirement asks for total groups, checking odd values only with remainder 1, dividing by zero, converting missing capacity to zero, assuming MOD syntax is universal, and expecting nonnegative output for every negative input.
Design mistakes include using ID modulo as random sampling, relying on simple modulo when server count changes, applying a function to indexed columns without measuring plans, mixing units, and presenting arithmetic allocation as actual processing status.
Best-Practice Checklist
- Use MOD when the requirement asks what remains after division.
- Use a zero remainder to test exact divisibility.
- Use a nonzero remainder for odd checks when negatives are possible.
- Require a valid nonzero divisor and usually a positive group size.
- Keep dividend, divisor, and remainder units compatible.
- Verify negative-operand semantics on the target DBMS.
- Use exact numeric types when exact decimal remainders matter.
- Distinguish complete groups, leftovers, and groups required.
- Base cycles on stable sequence values and deterministic ordering.
- Do not treat deterministic modulo selection as random sampling.
- Measure predicate performance and consider supported expression indexes.
- Test syntax, signs, NULL, zero divisors, types, and boundaries.
Interview Questions and Answers
What does MOD return? It returns the remainder after dividing the first numeric expression by the second.
What does MOD(17, 5) return? It returns 2 because 17 equals 5 times 3 plus 2.
How do you test whether an integer is even? Check whether its remainder after division by two is zero.
How do you test whether an integer is odd? Check whether its remainder after division by two is nonzero.
What is MOD versus FLOOR? MOD returns leftover units; FLOOR can return complete positive groups from the quotient.
What is MOD versus CEIL? MOD returns leftovers, while CEIL can return the number of groups required to contain all positive units.
What happens with NULL? A NULL dividend or divisor normally produces NULL.
What happens with a zero divisor? The operation is undefined; behavior varies, so validate or use a defensive expression.
Can MOD create cycles? Yes. Remainder classes repeat for a fixed positive divisor.
Why must negative values be tested? SQL implementations can differ in remainder sign and mathematical modulo expectations.
Key Takeaway
MOD answers one focused question: what remains after division. It is especially valuable for divisibility, even and odd checks, complete-group leftovers, partial batches, pagination remainders, and repeating cycles.
RELIABLE REMAINDER LOGIC = CORRECT DIVIDEND AND DIVISOR
+ NONZERO VALIDATED DIVISOR
+ COMPATIBLE UNITS AND TYPES
+ EXPLICIT NEGATIVE-VALUE SEMANTICS
+ STABLE CYCLE INPUTS
+ DBMS AND PERFORMANCE TESTING
Do not confuse remainder with quotient or capacity required. Handle zero and NULL deliberately, verify syntax and negative behavior on every target database, and preserve the arithmetic inputs for audit. With those safeguards, MOD becomes a precise tool for both simple divisibility and production grouping workflows.