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

SQL BETWEEN Command to fetch records from a range

Many times we may require to find out records between a range of value. We can specify one upper limit and one lower limit and the query should return all the records between these two values. The sql BETWEEN command will give us the required result in all these cases. 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 our upper limit is 75 and lower limit is 60. So we will get all the records within these limits and please 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).
Please 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 )

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

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>";

?>

Selecting records between two date ranges

▼ More on getting records from table with different combinations of commands