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);
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);
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.
INTERVAL(76000, Income) AS Res1,
INTERVAL(Income, 76000) AS Res2,
FROM `MySQL Tutorial`.customer
ORDER BY Income ASC;
Thank You for Visiting Our Blog