Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
5K+ Views
