MySQL Select IN range?

You cannot do select IN range. For the same result, use BETWEEN. Let us see an example −

IN(start,end): It means that the intermediate value between start and end won’t get displayed. For the above logic, you can use BETWEEN.

BETWEEN clause is inclusive, for example, suppose there are 1,2,3,4,5,6 numbers. If you want to display numbers from 2 to 6 inclusively, then using BETWEEN the numbers 2 and 6 will also get displayed.

Let us create a table −

mysql> create table SelectInWithBetweenDemo
   -> (
   -> PortalId int
   -> );
Query OK, 0 rows affected (0.77 sec)

Insert some records with the help of batch insert. The query is as follows −

mysql> insert into SelectInWithBetweenDemo values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15);
Query OK, 15 rows affected (0.19 sec)
Records: 15 Duplicates: 0 Warnings: 0

Display all records with the help of select statement. The query is as follows −

mysql> select *from SelectInWithBetweenDemo;

Here is the output −

+----------+
| PortalId |
+----------+
|        1 |
|        2 |
|        3 |
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
|       10 |
|       11 |
|       12 |
|       13 |
|       14 |
|       15 |
+----------+
15 rows in set (0.00 sec)

Let us now check the select IN range. The query is as follows −

mysql> select PortalId from SelectInWithBetweenDemo where PortalId IN(4,10);

The following is the output −

+----------+
| PortalId |
+----------+
|        4 |
|       10 |
+----------+
2 rows in set (0.00 sec)

Look at the above output, we are getting only 4 and 10, whereas we want the value 4,5,6,7,8,9,10.

Now we will use BETWEEN clause. It will give the result as we want with inclusion.

The query is as follows −

mysql> select PortalId from SelectInWithBetweenDemo where PortalId Between 4 and 10;

The following is the output −

+----------+
| PortalId |
+----------+
|        4 |
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
|       10 |
+----------+
7 rows in set (0.09 sec)

Suppose if you want exclusive property then you can use > and <. The query is as follows −

mysql> select PortalId from SelectInWithBetweenDemo where PortalId > 4 and PortalId < 10;

Here is the output −

+----------+
| PortalId |
+----------+
|        5 |
|        6 |
|        7 |
|        8 |
|        9 |
+----------+
5 rows in set (0.00 sec)