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:

</>

Copy

SELECT number1 % number2 AS remainder;

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

</>

Copy

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:

</>

Copy

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

Insert some sample data:

</>

Copy

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:

</>

Copy

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