Nov 2, 2013

BETWEEN keyword in sql server

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