How MOD works in SQL? Best MOD examples

How MOD works in SQL? Best MOD examples

When working with SQL, one of the lesser-known yet incredibly useful functions is MOD. This function helps us determine remainders after division, which can be handy in a variety of scenarios, such as distributing records into groups or filtering out certain sets of data.

What Is the MOD Function?

The MOD function in SQL returns the remainder of a division operation between two numbers. The syntax is quite simple:

MOD(dividend, divisor)

Here’s how it works in practice:

  • MOD(10, 3) returns 1 because 10 divided by 3 leaves a remainder of 1.
  • MOD(15, 4) returns 3 since 15 divided by 4 gives a remainder of 3.
  • MOD(20, 5) returns 0 because 20 is perfectly divisible by 5.

Why Use MOD in SQL?

The MOD function comes in handy in many real-world scenarios, such as:

  1. Distributing data into different groups in a cyclic manner.
  2. Selecting only even or odd rows from a dataset.
  3. Filtering entries based on alternative patterns.
  4. Certain financial and accounting calculations.

Practical Examples of Using MOD in SQL

1. Finding Even and Odd Numbers

One common use case is determining whether a number is even or odd. In SQL, this can be done with the MOD function:

SELECT number, 
       CASE 
           WHEN MOD(number, 2) = 0 THEN 'Even' 
           ELSE 'Odd' 
       END AS number_type
FROM numbers_table;

This query checks if a number is divisible by 2. If the remainder is 0, it’s even; otherwise, it’s odd.

2. Distributing Rows into Groups

Suppose we have a list of customers, and we want to assign them into 3 different groups for a marketing campaign:

SELECT customer_id, 
       MOD(customer_id, 3) AS group_number
FROM customers;

This approach effectively assigns customers into three different groups (0, 1, and 2) in a cyclic manner.

3. Finding Multiples of a Certain Number

We can also use MOD to filter out numbers that are multiples of a specific value. For example, to find all numbers in a table that are multiples of 5:

SELECT number FROM numbers_table 
WHERE MOD(number, 5) = 0;

This query returns only those numbers that are perfectly divisible by 5.

MOD Function Across Different SQL Dialects

The MOD function is widely supported across various SQL dialects, though some systems provide alternative ways to achieve the same result:

SQL Dialect MOD Function Support
MySQL Yes, MOD(a, b) or a % b
PostgreSQL Yes, MOD(a, b) or a % b
SQL Server Supports a % b, but not MOD(a, b)
Oracle Yes, using MOD(a, b)

Performance Considerations

While the MOD function is typically very fast, here are some things to keep in mind:

  • Avoid using MOD in indexed columns in WHERE clauses, as it can prevent index usage.
  • For large datasets, consider adding computed columns or pre-processing calculations for frequently used expressions.
  • Some databases optimize MOD calculations efficiently, but testing on your specific system is always a good idea.

Final Thoughts

The MOD function in SQL might not be the first tool you think of, but when used correctly, it can significantly simplify various queries. Whether it’s grouping data, filtering results, or partitioning records into logical categories, MOD remains an essential function for database professionals.

 

Other interesting article:

How ABS works in SQL? Best ABS examples