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

SQL BETWEEN Command to fetch records from a range