SQL BETWEEN command to select records within a range of MySQL table

Many times we may require to find out records between a range of values.
We can specify one lower limit and one upper limit for column and the query will return all the records between these two values.

SQL BETWEEN to get rows within a range with all other commands

We will apply here BETWEEN command to a numeric field and see how the records are returned from a Mysql table. Here is our table.

id	name	class	mark	sex
1	John Deo	Four	75	female
2	Max Ruin	Three	85	male
3	Arnold	Three	5	male
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
6	Alex John	Four	55	male

On this table we will apply our BETWEEN command to get all the records within some upper and lower limits. Say for our mark column upper limit is 75 and lower limit is 60. So we will get all the records within these limits and note that limit 60 and 75 both are inclusive. Here is our sql BETWEEN command.

SELECT * FROM `student`WHERE mark  BETWEEN 60 and 75

id	name	class	mark	sex
1	John Deo	Four	75	female
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
18	Honny	Five	75	male
20	Jackly	Nine	65	female
21	Babby John	Four	69	female
34	Gain Toe	Seven	69	male

You can see we have all the records between 60 and 75 ( both inclusive).
Note that we have to first start with lower limit and then upper limit. So the records between 60 and 75 will be displayed ( NOT BETWEEN 75 and 60 )

How many records are there within this limit ( USE count() )?

SELECT count(*) FROM student WHERE mark BETWEEN 60 and 75;

Using NOT

SELECT * FROM `student`WHERE mark NOT  BETWEEN 50 and 100

Output

id	name	class	mark	sex
19	Tinny	Nine	18	male

Displaying from highest to lowest mark

Using order by we can change the above display and show from highest to lowest.

SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 order by mark desc

By using DESC we are getting results from highest to lowest. By default it is from lowest range to highest range. Output is here

id	name	class	mark	sex
1	John Deo	Four	75	female
18	Honny	Five	75	male
21	Babby John	Four	69	female
34	Gain Toe	Seven	69	male
20	Jackly	Nine	65	female
4	Krish Star	Four	60	female

Displaying from one class only

We will restrict the students of class four only

SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 AND class ='Four'

id	name	class	mark	sex
1	John Deo	Four	75	female
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
21	Babby John	Four	69	female

Displaying from limited class using IN

Only from Class Four and Class Seven

SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 AND class IN('Four','Seven')

id	name	class	mark	sex
1	John Deo	Four	75	female
4	Krish Star	Four	60	female
5	John Mike	Four	60	female
21	Babby John	Four	69	female
34	Gain Toe	Seven	69	male

Displaying from limited class using NOT IN

SELECT * FROM `student`WHERE mark BETWEEN 60 and 75 AND class NOT IN('Four','Seven')

id	name	class	mark	sex
18	Honny	Five	75	male
20	Jackly	Nine	65	female

Number of students from each class

How many number of students have secured mark between 60 and 75 in each class by using GROUP BY

SELECT class, COUNT(id) as no   FROM `student` WHERE mark BETWEEN 60 AND 75 GROUP BY class

class	no
Five	1
Four	4
Nine	1
Seven	1

We can create a comparative statement in a grid view by using SQL IF and between command

SELECT class, 
SUM( IF( mark <50, 1, 0 ) ) AS grade_C, 
SUM( IF( mark BETWEEN 50 AND 70 , 1, 0 ) ) AS grade_B, 
SUM( IF( mark >70, 1, 0 ) ) AS grade_A 
FROM  `student` GROUP BY class

Out put is here

Class	grade_C	grade_B	grade_A
Eight	0	0	1
Five	0	0	3
Four	0	5	4
Nine	1	1	0
Seven	0	3	7
Six	0	1	6
Three	0	1	2

SQL IF condition

Sample code in PHP

Here is the sample code in PHP to to get data using between query.

connect to database using PHP PDO.

<?Php
require "config.php";// Database connection

$count="SELECT * FROM `student`WHERE mark  BETWEEN 60 and 75";

echo "<table>";
echo "<tr><th>id</th><th>name</th><th>class</th><th>mark</th></tr>";

foreach ($dbo->query($count) as $row) {
echo "<tr ><td>$row[id]</td><td>$row[name]</td><td>$row[class]</td><td>$row[mark]</td></tr>";
}

echo "</table>";

?>

Questions

What is the purpose of the "BETWEEN" operator in SQL?
How does the "BETWEEN" operator work in SQL?
What is the syntax for using the "BETWEEN" operator in SQL?
Can the "BETWEEN" operator be used with different data types in SQL? If yes, provide an example.
Does the "BETWEEN" operator include the boundary values in SQL? Explain with an example.
Can the "BETWEEN" operator be used with dates and timestamps in SQL? If yes, provide an example.
How can the "BETWEEN" operator be combined with other SQL clauses, such as WHERE or NOT, to create more complex queries?
Are there any performance considerations when using the "BETWEEN" operator in SQL? Explain.
Is the "BETWEEN" operator inclusive or exclusive in SQL? How can you modify it to be inclusive or exclusive?
Are there any alternatives to using the "BETWEEN" operator in SQL for achieving similar functionality?

Selecting records between two date ranges

Selecting records Case Condition Checking

Subscribe to our YouTube Channel here

alan	08-11-2010
so how would you have just the marks for id 4 and 5 display?

keerthana	14-09-2011
hi.. i need a mysql query for selecting transactions between two dates

Sam in Kenya	25-09-2011
Hie keerthana: SELECT date from jobvacancies WHERE `date` BETWEEN DATE_SUB( CURDATE( ) ,INTERVAL 12 MONTH ) AND DATE_SUB( CURDATE( ) ,INTERVAL 3 MONTH)

mebrahtu	28-11-2011
I have read (the SQL BETWEEN Command to fetch records from a range ) above. And it was very informative and helpful.thanks for who posted it.

rasheed	12-01-2012
i need a query of sql to get ten top student from table on base of their marks plz help any one

Jone	18-01-2012
@rasheed: SELECT * FROM Students ORDER BY Marks DESC LIMIT 10

Azim	20-02-2012
how to use BETWEEN command using upper limit and lower limit exclusive

reno rey quiza	03-10-2012
thanks a lot... this is really helpful... ! may you be returned a favor.. :))

PRAMOD	01-01-2013
TWO TABLES ARE GIVEN ,EMPLOYEES AND DEPARTMENT . DISPLAY THE NAMES OF ALL THE EMPLOYEES WHOSE SALARY IS NOT WITHIN THE RANGE FOR THE CORRESPONDING DEPARTMENT

mahamad	26-03-2013
i want to do pagination in jsp so how i will be able to do that?plz help me ...

Sajib	31-03-2010
thanks. this is very helpful...

Bas	14-01-2011
SELECT * FROM `student` WHERE id=4 OR id=5

Coder	29-08-2013
i want to get the supplier details from a table named supplier by giving supplier code and between two dates also without supplier code

Priya	15-07-2014
I need a query which will select only those data whose length of data is within 10 to 12 and those length is greater or equal to 10, should start with 91 or 0

smo	30-08-2013
SELECT * FROM `supplier_table` WHERE dt BETWEEN 2005-01-01 AND 2005-12-31 and supplier_id=1234 You can read more on query using two date ranges here.

omkar	10-12-2014
I need a query to count duplicate data between two dates.

ravi	25-02-2015
i need to display three columns as name,count(range 5-10),count(range 6-10), the source table contain two columns i.e name and range example name range aaaa 5 bbbb 9 aaaa 6 aaaa 7 bbbb 9 aaaa 5 and i need output as name count(range 0-5) count(range 6-10 ) aaaa 2 2 bbbb 0 2

Yusuf Ibrahim	04-05-2015
Please I need help in php and mysql, a code that will automatically generates position for a students according to their total marks.thank

Chandu	26-06-2015
How to select only those rows whose sex is male....?

smo1234	27-06-2015
You can use Order by to list students according to their mark.

SHIV PANKAJ	23-02-2016
I have a table having record in one column like "2016-01-02 19:45:31.000". I have to select record with time between 19:40 and 19:50 for whole month. Kindly provide me the query.......

chandu	03-03-2016
hi i need a query to fetch records for every 6 hrs interval in a day i.e., 4 times a day from a table

smo1234	10-08-2018
Query using different time along with date like records between 9 to 18 hours of a particular date , all records with time 10 to 12 hours , all records group by hour etc are explained at DATE_SUB() and BETWEEN queries here.

SQL BETWEEN Command to fetch records from a range