MySQL INTERVAL Operator

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 show how to use the Interval Operator with multiple examples, and the syntax is:

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

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

MySQL Interval Operator Example

In this example, we use 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);
MySQL interval function Example 1

Within the first statement, we used the N value less than N1 to return 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 MySQL 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);
INTERVAL Example 2

Interval Example 2

The following MySQL operator query finds the interval position of a Customer based on the value 76000. First, 76000 acts 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, this 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 customer
 ORDER BY Income ASC;
MySQL INTERVAL Operator 3

About Suresh

Suresh is the founder of TutorialGateway and a freelance software developer. He specialized in Designing and Developing Windows and Web applications. The experience he gained in Programming and BI integration, and reporting tools translates into this blog. You can find him on Facebook or Twitter.