SQL Keywords

SQL MOD

What is the SQL MOD function?

Returns the remainder after dividing one numeric expression by another.
Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.

Compatible dialects for SQL MOD: PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, BigQuery, Redshift

SQL MOD Full Explanation

SQL MOD is a deterministic arithmetic function that calculates the remainder of an integer or exact numeric division. The dividend is divided by the divisor, and only the leftover part is returned. Most engines support it as a function (MOD(dividend, divisor)); some also expose the percent sign (%) operator as shorthand. The result carries the same sign as the dividend in ANSI-compliant systems, though a few legacy engines mirror the sign of the divisor. Division by zero raises an error. When either argument is NULL, the function returns NULL. If inputs are non-integer numerics, many systems truncate them to integers before performing the division.

SQL MOD Syntax

MOD(dividend, divisor);

SQL MOD Parameters

  • dividend (Numeric) - The value to be divided.
  • divisor (Numeric) - The value that divides the dividend. Must not be zero.

Example Queries Using SQL MOD

-- Basic remainder
SELECT MOD(10, 3);

-- Find even order IDs
SELECT order_id
FROM orders
WHERE MOD(order_id, 2) = 0;

-- Segment salaries by 1,000-unit buckets
SELECT employee_id,
       MOD(salary, 1000) AS salary_remainder
FROM employees;

Expected Output Using SQL MOD

  • 1) Returns 1 for MOD(10, 3)
  • 2) Returns only rows whose order_id divides evenly by 2
  • 3) Adds a salary_remainder column showing the leftover portion after removing thousands

Use Cases with SQL MOD

  • Detect even or odd values.
  • Distribute rows across N partitions for sharding or hashing.
  • Cycle through a set of labels (e.g., weekdays) by index.
  • Trim trailing thousands, hundreds, or tens to group numeric data.
  • Build round-robin workloads or pagination logic.

Common Mistakes with SQL MOD

  • Dividing by zero – triggers runtime error.
  • Expecting negative remainders to always be positive across engines.
  • Forgetting NULL yields NULL and filtering incorrectly.
  • Mixing with floating-point types and assuming fractional remainders.
  • Confusing MOD() with the string function SUBSTRING().

Related Topics

% operator, FLOOR(), CEIL(), ABS(), DIV, arithmetic operators

First Introduced In

SQL:1999

Frequently Asked Questions

What data types does MOD accept?

Any exact numeric type (INTEGER, BIGINT, NUMERIC, DECIMAL). Floating types are accepted in some engines but are implicitly converted to integers.

How does MOD handle negative numbers?

ANSI-compliant engines give the result the same sign as the dividend. Verify engine-specific behavior if sign matters.

Is MOD faster than using % in WHERE clauses?

Most optimizers treat MOD() and % identically, so performance is the same. Use whichever style matches team conventions.

Can MOD be indexed?

Because MOD alters the value, indexes on the original column are not used directly. Consider computed columns or partial indexes if performance is critical.

Sign up to get up to date news on SQL keywords
Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)
Oops! Something went wrong while submitting the form.
Trusted by top engineers on high-velocity teams
Aryeo Logo
Assort Health
Curri
Rubie Logo
Bauhealth Logo
Truvideo Logo

Check out other commonly used SQL Keywords!