BETWEEN is a range tester which can be used either in WHERE clause or HAVING clause.
Syntax:
<Test_Expression> [NOT] BETWEEN <Initial_Expression> AND <Ending_Expression>
Where:
<Test_Expression>: It any valid expression which to be test that it value is within the <Initial_Expression> and <Ending_Expression> or not. It is commonly name of the column of a table.
<Initial_Expression> and <Ending_Expression>: It is the boundary values.
NOT: It is optional keyword which negates the meaning.
Return type: It return a Boolean value.
Note: Data type of <Test_Expression>, <Initial_Expression> and <Ending_Expression> must be same or implicitly convertible.
For example: Suppose RollNo is column of type integer
RollNo BETWEEN 1 AND 100
Above statement will return true if and only if 1 <= RollNo <= 100
RollNo NOT BETWEEN 1 AND 100
Above statement will return true if and only if RollNo < 1 or RollNo > 100
Consider we have Employee table which look like this:
EmpID
|
EmpName
|
DateOfJoining
|
Age
|
100
|
Sachin Tendulker
|
2008-12-05
|
35
|
101
|
Ricky Ponting
|
2001-06-08
|
40
|
102
|
Brian Lara
|
2000-03-28
|
42
|
103
|
Stephen Fleming
|
1987-11-01
|
54
|
104
|
Shaun Pollock
|
2001-11-22
|
32
|
105
|
Shane Warne
|
1988-04-21
|
51
|
106
|
Shoaib Akhtar
|
1990-09-17
|
42
|
107
|
Muttiah Muralitharan
|
2002-07-07
|
36
|
108
|
Andrew Flintoff
|
2004-05-30
|
33
|
Using BETWEEN with varchar:
SELECT *
FROM Employee
WHERE EmpName BETWEEN 'Sho' AND 'T'
BETWEEN statement will return true if and only if EmpName is start from Sho upto T same as dictionary. It is not case sensitive.
Output will be:
EmpID
|
EmpName
|
DateOfJoining
|
Age
|
103
|
Stephen Fleming
|
1987-11-01
|
54
|
106
|
Shoaib Akhtar
|
1990-09-17
|
42
|
Using BETWEEN with date:
SELECT *
FROM Employee
WHERE DateOfJoining BETWEEN '2001-11-22' AND '2008-12-05'
It will select all the employees whose DataOfJoining is between 2001-11-22 to 2008-12-05 including 2001-11-22 and 2008-12-05
Output will be:
EmpID
|
EmpName
|
DateOfJoining
|
Age
|
100
|
Sachin Tendulker
|
2008-12-05
|
35
|
104
|
Shaun Pollock
|
2001-11-22
|
32
|
107
|
Muttiah Muralitharan
|
2002-07-07
|
36
|
108
|
Andrew Flintoff
|
2004-05-30
|
33
|
Using BETWEEN in having clause:
SELECT Age,COUNT(*) AS Total
FROM Employee
GROUP BY Age
HAVING Age BETWEEN 40 AND 50
Output:
Age
|
Total
|
40
|
1
|
42
|
2
|
No comments:
Post a Comment