Tutorial Gateway

MySQL INTERVAL Operator

by

The MySQL Interval Operator uses the binary search method to search the items, and returns the values from 0 to N.

In this article, we will show you, How to use the MySQL Interval Operator with multiple examples.

MySQL Interval Operator Syntax

The basic syntax of the interval in MySQL can be written as:

SELECT INTERVAL (N, N1, N2, N3,....Nn)

If N < N1 then 0 is returned. N < N2 means 1, N < N3 means 2 etc. This function requires the arguments in ascending order. I mean, N1 < N2 < N3 < N4…….< Nn. Otherwise, it won’t work.

MySQL Interval Example

In this example, we are using the same values from N1 to N7. However, we are changing the N values to check the interval position.

SELECT INTERVAL(45, 10, 20, 30, 40, 50, 60, 70);

SELECT INTERVAL(11, 10, 20, 30, 40, 50, 60, 70);

SELECT INTERVAL(67, 10, 20, 30, 40, 50, 60, 70);

OUTPUT

MySQL INTERVAL Operator 1

Within the first statement, we used the N value less than N1 so, it returned 0. Next, we used the N Value greater than N7 so, 7 has returned. Within the third statement, we used NULL as N. This is the reason, it has returned -1.

SELECT INTERVAL(6, 10, 20, 30, 40, 50, 60, 70);

SELECT INTERVAL(98, 10, 20, 30, 40, 50, 60, 70);

SELECT INTERVAL(NULL, 10, 20, 30, 40, 50, 60, 70);

OUTPUT

MySQL INTERVAL Operator 2

MySQL Interval Operator Example 2

The following query finds the interval position of a Customer based on the value 76000. First, 76000 act as N and employee income as N1. Next, Income acts as N and 76000 as N1.

Here, we used Order By clause to sort the Income in ascending order otherwise, Interval function won’t work.

SELECT EmpID, 
       `First Name`,
       `Last Name`,
       Qualification,
       Occupation,
       Income,
       INTERVAL(76000, Income) AS Res1,
       INTERVAL(Income, 76000) AS Res2,
       Sales
 FROM `MySQL Tutorial`.customer
 ORDER BY Income ASC;

OUTPUT

MySQL INTERVAL Operator 3

Thank You for Visiting Our Blog