CODEWITHSUNDEEP
mysqlsql-server
Pearl
Pearl

Reputation: 9435

Between Operator issue

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

Upvotes: 0

Views: 98

Answers (2)

Ajay2707
Ajay2707

Reputation: 5798

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

Upvotes: 1

Bohemian
Bohemian

Reputation: 425033

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.

Upvotes: 3

Related Questions