SQL Modulus Operator

SQL Modulus Operator

The SQL MOD operator (or % symbol in some databases) returns the remainder of a division operation. This operator is useful in scenarios such as checking divisibility, filtering even or odd numbers, and performing cyclic calculations.

In this tutorial, we will explore the SQL modulus operator, its syntax, and practical examples.

---

Syntax of SQL Modulus Operator

The syntax of the SQL modulus operator varies slightly depending on the database system:

SELECT number1 % number2 AS remainder;

Alternatively, in databases like MySQL and PostgreSQL, you can use:

SELECT MOD(number1, number2) AS remainder;

Explanation:

• number1: The dividend (the number being divided).
• number2: The divisor (the number dividing number1).
• The result is the remainder after division.

---

Step-by-Step Examples Using SQL Modulus Operator

1. Checking Odd and Even Numbers

Let’s create a table employees with employee IDs and names:

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Insert some sample data:

INSERT INTO employees (name)
VALUES ('Arjun'), ('Ram'), ('John'), ('Roy');

Now, let’s use the modulus operator to find out which employee IDs are odd or even:

SELECT id, name, 
    CASE 
        WHEN id % 2 = 0 THEN 'Even ID'
        ELSE 'Odd ID'
    END AS id_type
FROM employees;

Explanation:

• The % 2 operation checks if the ID is divisible by 2.
• If the remainder is 0, it’s an even number; otherwise, it’s odd.
• The CASE statement labels the IDs as “Even ID” or “Odd ID”.

---

2. Distributing Players into Two Teams

Suppose we have a list of players, and we want to assign them alternately to two teams based on their ID numbers.

CREATE TABLE players (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Insert sample data:

INSERT INTO players (name)
VALUES ('Arjun'), ('Ram'), ('John'), ('Roy'), ('Steve'), ('Mark');

Use the modulus operator to divide them into two teams:

SELECT id, name, 
    CASE 
        WHEN id % 2 = 0 THEN 'Team B'
        ELSE 'Team A'
    END AS assigned_team
FROM players;

Explanation:

• The modulus operator (% 2) is used to alternate between two teams.
• If the remainder is 0, the player is assigned to “Team B”; otherwise, they go to “Team A”.
• This ensures an even distribution of players between the two teams.

---

Conclusion

In this tutorial for SQL modulus operator (% or MOD()), we covered:

1. The syntax of the modulus operator.
2. How to determine odd and even numbers in a dataset.
3. Using the modulus operator for team assignments.