Between Operator issue

Question

tblEmployee

ID  Name  Salary
1   E1    10000
2   E2    20000
3   E3    30000
4   E4    40000
5   E5    50000

Query:

select * from tblEmployee where Salary between 10000 AND 40000

Output:

1   E1    10000
2   E2    20000
3   E3    30000
4   E4    40000

But when I use the below query, I got no results. Why?

select * from tblEmployee where Salary between 40000 AND 10000

See this: http://stackoverflow.com/questions/749615/does-ms-sql-servers-between-include-the-range-boundaries — SMA, Nov 21 '14 at 07:20

score 3 · Accepted Answer · answered Nov 21 '14 at 07:18

3

Between has the syntax:

val BETWEEN low AND high

And is syntactic sugar for:

val >= low AND val <= high

So your results are expected, since it is impossible for any value to be greater than 40000 and less than 10000.

answered Nov 21 '14 at 07:18

Bohemian

412,405
93
575
722

score 1 · Answer 2 · answered Nov 21 '14 at 07:50

Between used for the range. We have to define from starting point (low value) to ending point(high value) always to get result.

So as not get the result, the reason is high to low not work. Even though for text search it always apply the same rule. Some example which works i.e.

BETWEEN 10 AND 20     
BETWEEN 'C' AND 'M'
BETWEEN #07/04/1996# AND #07/09/1996#;

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

http://msdn.microsoft.com/en-IN/library/ms187922.aspx

Between Operator issue

2 Answers2