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`WHEREmarkBETWEEN60AND75
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() )?